//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

/* Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */


using System;
using System.Text;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using EffiProzDB.DataTypes;
using EffiProzDB.Lib;
using EffiProzDB.Persist;
using EffiProzDB.ScriptIO;

namespace EffiProzDB
{


    // fredt@users - 1.7.2 - structural modifications to allow inheritance
    // boucherb@users - 1.7.2 - 20020225
    // - factored _out all reusable code into DIXXX support classes
    // - completed Fred's work on allowing inheritance
    // boucherb@users - 1.7.2 - 20020304 - bug fixes, refinements, better java docs
    // fredt@users - 1.8.0 - updated to report latest enhancements and changes
    // boucherb@users - 1.8.0 - 20050515 - furhter SQL 2003 metadata support

    /**
     *:DatabaseInformationMain to provide additional system table
     * support. <p>
     *
     * @author boucherb@users
     * @version 1.8.0
     * @since 1.7.2
     */
    class DatabaseInformationFull : DatabaseInformationMain
    {

        /** Provides SQL function/procedure reporting support. */
        protected DIProcedureInfo pi;

        /**
         * Constructs a new DatabaseInformationFull instance. <p>
         *
         * @param db the database for which to produce system tables.
         * @  if a database access error occurs.
         */
        public DatabaseInformationFull(Database db)
            : base(db)
        {


            pi = new DIProcedureInfo(ns);
        }

        /**
         * Retrieves the system table corresponding to the specified index. <p>
         *
         * @param tableIndex index identifying the system table to generate
         * @  if a database access error occurs
         * @return the system table corresponding to the specified index
         */
        protected override Table generateTable(int tableIndex)
        {

            switch (tableIndex)
            {

                case _SYSTEM_PROCEDURECOLUMNS:
                    return SYSTEM_PROCEDURECOLUMNS();

                case _SYSTEM_PROCEDURES:
                    return SYSTEM_PROCEDURES();

                case _SYSTEM_SUPERTABLES:
                    return SYSTEM_SUPERTABLES();

                case _SYSTEM_SUPERTYPES:
                    return SYSTEM_SUPERTYPES();

                case _SYSTEM_UDTATTRIBUTES:
                    return SYSTEM_UDTATTRIBUTES();

                case _SYSTEM_UDTS:
                    return SYSTEM_UDTS();

                case _SYSTEM_VERSIONCOLUMNS:
                    return SYSTEM_VERSIONCOLUMNS();

                // HSQLDB-specific
                case _SYSTEM_ALIASES:
                    return SYSTEM_ALIASES();

                case _SYSTEM_CACHEINFO:
                    return SYSTEM_CACHEINFO();

                case _SYSTEM_CLASSPRIVILEGES:
                    return SYSTEM_CLASSPRIVILEGES();

                case _SYSTEM_SESSIONINFO:
                    return SYSTEM_SESSIONINFO();

                case _SYSTEM_PROPERTIES:
                    return SYSTEM_PROPERTIES();

                case _SYSTEM_SESSIONS:
                    return SYSTEM_SESSIONS();

                case _SYSTEM_TRIGGERCOLUMNS:
                    return SYSTEM_TRIGGERCOLUMNS();

                case _SYSTEM_TRIGGERS:
                    return SYSTEM_TRIGGERS();

                case _SYSTEM_VIEWS:
                    return SYSTEM_VIEWS();

                case _SYSTEM_TEXTTABLES:
                    return SYSTEM_TEXTTABLES();

                case _SYSTEM_USAGE_PRIVILEGES:
                    return SYSTEM_USAGE_PRIVILEGES();

                case _SYSTEM_CHECK_COLUMN_USAGE:
                    return SYSTEM_CHECK_COLUMN_USAGE();

                case _SYSTEM_CHECK_ROUTINE_USAGE:
                    return SYSTEM_CHECK_ROUTINE_USAGE();

                case _SYSTEM_CHECK_TABLE_USAGE:
                    return SYSTEM_CHECK_TABLE_USAGE();

                case _SYSTEM_TABLE_CONSTRAINTS:
                    return SYSTEM_TABLE_CONSTRAINTS();

                case _SYSTEM_VIEW_TABLE_USAGE:
                    return SYSTEM_VIEW_TABLE_USAGE();

                case _SYSTEM_VIEW_COLUMN_USAGE:
                    return SYSTEM_VIEW_COLUMN_USAGE();

                case _SYSTEM_VIEW_ROUTINE_USAGE:
                    return SYSTEM_VIEW_ROUTINE_USAGE();

                case _SYSTEM_AUTHORIZATIONS:
                    {
                        return SYSTEM_AUTHORIZATIONS();
                    }
                case _SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS:
                    {
                        return SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS();
                    }
                case _SYSTEM_SCHEMATA:
                    {
                        return SYSTEM_SCHEMATA();
                    }
                case _SYSTEM_COLLATIONS:
                    {
                        return SYSTEM_COLLATIONS();
                    }
                default:
                    return base.generateTable(tableIndex);
            }
        }

        /**
         * Retrieves a <code>Table</code> object describing the aliases defined
         * within this database. <p>
         *
         * Currently two types of alias are reported: DOMAIN alaises (alternate
         * names for column data types when issuing "CREATE TABLE" DDL) and
         * ROUTINE aliases (alternate names that can be used when invoking
         * routines as SQL functions or stored procedures). <p>
         *
         * Each row is an alias description with the following columns: <p>
         *
         * <pre class="SqlCodeExample">
         * OBJECT_TYPE  VARCHAR   type of the aliased object
         * OBJECT_CAT   VARCHAR   catalog of the aliased object
         * OBJECT_SCHEM VARCHAR   schema of the aliased object
         * OBJECT_NAME  VARCHAR   simple identifier of the aliased object
         * ALIAS_CAT    VARCHAR   catalog _in which alias is defined
         * ALIAS_SCHEM  VARCHAR   schema _in which alias is defined
         * ALIAS        VARCHAR   alias for the indicated object
         * </pre> <p>
         *
         * <b>Note:</b> Up to and including HSQLDB 1.7.2, user-defined aliases
         * are supported only for SQL function and stored procedure calls
         * (indicated by the value "ROUTINE" _in the OBJECT_TYPE
         * column), and there is no syntax for dropping aliases, only for
         * creating them. <p>
         * @return a Table object describing the accessisble
         *      aliases _in the context of the calling session
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_ALIASES()
        {

            Table t = sysTables[_SYSTEM_ALIASES];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_ALIASES]);

                addColumn(t, "OBJECT_TYPE", Types.VARCHAR, 32, false);    // not null
                addColumn(t, "OBJECT_CAT", Types.VARCHAR);
                addColumn(t, "OBJECT_SCHEM", Types.VARCHAR);
                addColumn(t, "OBJECT_NAME", Types.VARCHAR, false);        // not null
                addColumn(t, "ALIAS_CAT", Types.VARCHAR);
                addColumn(t, "ALIAS_SCHEM", Types.VARCHAR);
                addColumn(t, "ALIAS", Types.VARCHAR, false);              // not null

                // order: OBJECT_TYPE, OBJECT_NAME, ALIAS.
                // true PK.
                t.createPrimaryKey(null, new int[] {
                0, 3, 6
            }, true);

                return t;
            }

            // Holders for calculated column values
            string cat;
            string schem;
            string alias;
            string objName;
            string objType;

            // Intermediate holders
            string className;
            Dictionary<string, String> hAliases;

            Object[] row;
            int pos;

            // Column number mappings
            const int ialias_object_type = 0;
            const int ialias_object_cat = 1;
            const int ialias_object_schem = 2;
            const int ialias_object_name = 3;
            const int ialias_cat = 4;
            const int ialias_schem = 5;
            const int ialias = 6;

            // Initialization
            hAliases = database.getAliasMap();

            objType = "ROUTINE";

            // Do it.
            foreach (var apair in hAliases)
            {
                row = t.getEmptyRowData();
                alias = apair.Key;
                objName = apair.Value;

                // must have class grant to see method call aliases
                pos = objName.LastIndexOf('.');

                if (pos <= 0)
                {

                    // should never occur _in practice, as this is typically a Java
                    // method name, but there's nothing preventing a user from
                    // creating an alias entry that is not _in method FQN form;
                    // such entries are not illegal, only useless.  Probably,
                    // we should eventually try to disallow them.
                    continue;
                }

                className = objName.Substring(0, pos - (0));

                if (!session.isAccessible(className))
                {
                    continue;
                }

                cat = ns.getCatalogName(objName);
                schem = ns.getSchemaName(className);
                row[ialias_object_type] = objType;
                row[ialias_object_cat] = cat;
                row[ialias_object_schem] = schem;
                row[ialias_object_name] = objName;
                row[ialias_cat] = cat;
                row[ialias_schem] = schem;
                row[ialias] = alias;

                t.insertSys(row);
            }

            // must have create/alter table rights to see domain aliases
            if (session.isAdmin())
            {

                objType = "DOMAIN";

                foreach (var pair in Types.typeAliases)
                {
                    row = t.getEmptyRowData();
                    alias = pair.Key;

                    int tn = pair.Value;

                    objName = Types.getTypeString(tn);

                    if (alias.Equals(objName))
                    {
                        continue;
                    }

                    cat = ns.getCatalogName(objName);
                    schem = ns.getSchemaName(objName);
                    row[ialias_object_type] = objType;
                    row[ialias_object_cat] = cat;
                    row[ialias_object_schem] = schem;
                    row[ialias_object_name] = objName;
                    row[ialias_cat] = cat;
                    row[ialias_schem] = schem;
                    row[ialias] = alias;

                    t.insertSys(row);
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the current
         * state of all row caching objects for the accessible
         * tables defined within this database. <p>
         *
         * Currently, the row caching objects for which state is reported are: <p>
         *
         * <OL>
         * <LI> the system-wide <code>Cache</code> object used by CACHED tables.
         * <LI> any <code>TextCache</code> objects _in use by [TEMP] TEXT tables.
         * </OL> <p>
         *
         * Each row is a cache object state description with the following
         * columns: <p>
         *
         * <pre class="SqlCodeExample">
         * CACHE_FILE          VARCHAR   absolute path of cache data file
         * MAX_CACHE_SIZE      INTEGER   maximum allowable cached Row objects
         * MAX_CACHE_BYTE_SIZE INTEGER   maximum allowable size of cached Row objects
         * CACHE_LENGTH        INTEGER   number of data bytes currently cached
         * CACHE_SIZE          INTEGER   number of rows currently cached
         * FREE_BYTES          INTEGER   total bytes _in available file allocation units
         * FREE_COUNT          INTEGER   total # of allocation units available
         * FREE_POS            INTEGER   largest file position allocated + 1
         * </pre> <p>
         *
         * <b>Notes:</b> <p>
         *
         * <code>TextCache</code> objects do not maintain a free list because
         * deleted rows are only marked deleted and never reused. As such, the
         * columns FREE_BYTES, SMALLEST_FREE_ITEM, LARGEST_FREE_ITEM, and
         * FREE_COUNT are always reported as zero for rows reporting on
         * <code>TextCache</code> objects. <p>
         *
         * Currently, CACHE_SIZE, FREE_BYTES, SMALLEST_FREE_ITEM, LARGEST_FREE_ITEM,
         * FREE_COUNT and FREE_POS are the only dynamically changing values.
         * All others are constant for the life of a cache object. In a future
         * release, other column values may also change over the life of a cache
         * object, as SQL syntax may eventually be introduced to allow runtime
         * modification of certain cache properties. <p>
         *
         * @return a description of the current state of all row caching
         *      objects associated with the accessible tables of the database
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_CACHEINFO()
        {

            Table t = sysTables[_SYSTEM_CACHEINFO];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_CACHEINFO]);

                addColumn(t, "CACHE_FILE", Types.VARCHAR, false);         // not null
                addColumn(t, "MAX_CACHE_COUNT", Types.INTEGER, false);    // not null
                addColumn(t, "MAX_CACHE_BYTES", Types.BIGINT, false);     // not null
                addColumn(t, "CACHE_SIZE", Types.INTEGER, false);         // not null
                addColumn(t, "CACHE_BYTES", Types.BIGINT, false);         // not null
                addColumn(t, "FILE_FREE_BYTES", Types.INTEGER, false);    // not null
                addColumn(t, "FILE_FREE_COUNT", Types.INTEGER, false);    // not null
                addColumn(t, "FILE_FREE_POS", Types.BIGINT, false);       // not null
                t.createPrimaryKey(null, new int[] { 0 }, true);

                return t;
            }

            DataFileCache cache;
            Object[] row;
            HashSet<DataFileCache> cacheSet;
            //IEnumerable<DataFileCache> caches;
            IEnumerable<Table> tables;
            //int iFreeBytes;
            //int iLargestFreeItem;
            //long lSmallestFreeItem;

            // column number mappings
            const int icache_file = 0;
            const int imax_cache_sz = 1;
            const int imax_cache_bytes = 2;
            const int icache_size = 3;
            const int icache_length = 4;
            const int ifree_bytes = 5;
            const int ifree_count = 6;
            const int ifree_pos = 7;

            // Initialization
            cacheSet = new HashSet<DataFileCache>();

            // dynamic system tables are never cached
            tables = database.schemaManager.allTablesIterator();

            foreach (var table in tables)
            {


                if (table.isFileBased() && isAccessibleTable(table))
                {
                    cache = table.getCache();

                    if (cache != null)
                    {
                        cacheSet.Add(cache);
                    }
                }
            }



            // Do it.
            foreach (var tcache in cacheSet)
            {
                cache = tcache;
                row = t.getEmptyRowData();
                row[icache_file] =
                    FileUtil.GetDefaultInstance().CanonicalOrAbsolutePath(
                        cache.getFileName());
                row[imax_cache_sz] = (cache.capacity());
                row[imax_cache_bytes] = (cache.bytesCapacity());
                row[icache_size] = (cache.getCachedObjectCount());
                row[icache_length] =
                     (cache.getTotalCachedBlockSize());
                row[ifree_bytes] = (cache.getTotalFreeBlockSize());
                row[ifree_count] = (cache.getFreeBlockCount());
                row[ifree_pos] = (cache.getFileFreePos());

                t.insertSys(row);
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the visible
         * access rights for all accessible Java Class objects defined
         * within this database.<p>
         *
         * Each row is a Class privilege description with the following
         * columns: <p>
         *
         * <pre class="SqlCodeExample">
         * CLASS_CAT    VARCHAR   catalog _in which the class is defined
         * CLASS_SCHEM  VARCHAR   schema _in which the class is defined
         * CLASS_NAME   VARCHAR   fully qualified name of class
         * GRANTOR      VARCHAR   grantor of access
         * GRANTEE      VARCHAR   grantee of access
         * PRIVILEGE    VARCHAR   name of access: {"EXECUTE" | "TRIGGER"}
         * IS_GRANTABLE VARCHAR   grantable?: {"YES" | "NO" | NULL (unknown)}
         * </pre>
         *
         * <b>Note:</b> Users with the administrative privilege implicily have
         * full and unrestricted access to all Classes available to the database
         * class loader.  However, only explicitly granted rights are reported
         * _in this table.  Explicit Class grants/revokes to admin users have no
         * effect _in reality, but are reported _in this table anyway for
         * completeness. <p>
         *
         * @return a <code>Table</code> object describing the visible
         *        access rights for all accessible Java Class
         *        objects defined within this database
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_CLASSPRIVILEGES()
        {

            Table t = sysTables[_SYSTEM_CLASSPRIVILEGES];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_CLASSPRIVILEGES]);

                addColumn(t, "CLASS_CAT", Types.VARCHAR);
                addColumn(t, "CLASS_SCHEM", Types.VARCHAR);
                addColumn(t, "CLASS_NAME", Types.VARCHAR, false);         // not null
                addColumn(t, "GRANTOR", Types.VARCHAR, false);            // not null
                addColumn(t, "GRANTEE", Types.VARCHAR, false);            // not null
                addColumn(t, "PRIVILEGE", Types.VARCHAR, 7, false);       // not null
                addColumn(t, "IS_GRANTABLE", Types.VARCHAR, 3, false);    // not null
                t.createPrimaryKey(null, new int[] {
                2, 4, 5
            }, true);

                return t;
            }

            // calculated column values
            string clsCat;
            string clsSchem;
            string clsName;
            string grantorName;
            string granteeName;
            string privilege;
            string isGrantable;

            // intermediate holders
            UserManager um;
            List<User> users;
            HashSet<string> classNameSet;
            IEnumerable<string> classNames;
            User granteeUser;
            Object[] row;

            // column number mappings
            const int icls_cat = 0;
            const int icls_schem = 1;
            const int icls_name = 2;
            const int igrantor = 3;
            const int igrantee = 4;
            const int iprivilege = 5;
            const int iis_grntbl = 6;

            // Initialization
            grantorName = GranteeManager.DBA_ADMIN_ROLE_NAME;
            um = database.getUserManager();
            users = um.listVisibleUsers(session, true);

            // Do it.
            for (int i = 0; i < users.Count; i++)
            {
                granteeUser = (User)users[i];
                granteeName = granteeUser.getName();
                isGrantable = granteeUser.isAdmin() ? "YES"
                                                     : "NO";
                classNameSet = granteeUser.getGrantedClassNames(false);

                if (granteeUser.isPublic())
                {
                    ns.addBuiltinToSet(classNameSet);
                }



                // boucherb@users 20030305 - TODO completed.
                // "EXECUTE" is closest to correct (from: SQL 200n ROUTINE_PRIVILEGES)
                // There is nothing even like CLASS_PRIVILEGES table under SQL 200n spec.
                privilege = "EXECUTE";

                foreach (var c in classNameSet)
                {
                    clsName = c;
                    clsCat = ns.getCatalogName(clsName);
                    clsSchem = ns.getSchemaName(clsName);
                    row = t.getEmptyRowData();
                    row[icls_cat] = clsCat;
                    row[icls_schem] = clsSchem;
                    row[icls_name] = clsName;
                    row[igrantor] = grantorName;
                    row[igrantee] = granteeName;
                    row[iprivilege] = privilege;
                    row[iis_grntbl] = isGrantable;

                    t.insertSys(row);
                }

                classNames = ns.iterateAccessibleTriggerClassNames(granteeUser);

                // boucherb@users 20030305 - TODO completed.
                // "TRIGGER" is closest to correct. (from: SQL 200n TABLE_PRIVILEGES)
                // There is nothing even like CLASS_PRIVILEGES table under SQL 200n spec.
                privilege = "TRIGGER";

                foreach (var cc in classNames)
                {
                    clsName = cc;
                    clsCat = ns.getCatalogName(clsName);
                    clsSchem = ns.getSchemaName(clsName);
                    row = t.getEmptyRowData();
                    row[icls_cat] = clsCat;
                    row[icls_schem] = clsSchem;
                    row[icls_name] = clsName;
                    row[igrantor] = grantorName;
                    row[igrantee] = granteeName;
                    row[iprivilege] = privilege;
                    row[iis_grntbl] = isGrantable;

                    t.insertSys(row);
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing attributes
         * for the calling session context.<p>
         *
         * The rows report the following {key,value} pairs:<p>
         *
         * <pre class="SqlCodeExample">
         * KEY (VARCHAR)       VALUE (VARCHAR)
         * ------------------- ---------------
         * SESSION_ID          the id of the calling session
         * AUTOCOMMIT          YES: session is _in autocommit mode, else NO
         * USER                the name of user connected _in the calling session
         * (was READ_ONLY)
         * SESSION_READONLY    TRUE: session is _in read-only mode, else FALSE
         * (new)
         * DATABASE_READONLY   TRUE: database is _in read-only mode, else FALSE
         * MAXROWS             the MAXROWS setting _in the calling session
         * DATABASE            the name of the database
         * IDENTITY            the last identity value used by calling session
         * </pre>
         *
         * <b>Note:</b>  This table <em>may</em> become deprecated _in a future
         * release, as the information it reports now duplicates information
         * reported _in the newer SYSTEM_SESSIONS and SYSTEM_PROPERTIES
         * tables. <p>
         *
         * @return a <code>Table</code> object describing the
         *        attributes of the connection associated
         *        with the current execution context
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_SESSIONINFO()
        {

            Table t = sysTables[_SYSTEM_SESSIONINFO];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_SESSIONINFO]);

                addColumn(t, "KEY", Types.VARCHAR, false);      // not null
                addColumn(t, "VALUE", Types.VARCHAR, false);    // not null
                t.createPrimaryKey(null);

                return t;
            }

            Object[] row;

            row = t.getEmptyRowData();
            row[0] = "SESSION_ID";
            row[1] = (session.getId());

            t.insertSys(row);

            row = t.getEmptyRowData();
            row[0] = "AUTOCOMMIT";
            row[1] = session.isAutoCommit() ? "TRUE"
                                            : "FALSE";

            t.insertSys(row);

            row = t.getEmptyRowData();
            row[0] = "USER";
            row[1] = session.getUsername();

            t.insertSys(row);

            row = t.getEmptyRowData();
            row[0] = "SESSION_READONLY";
            row[1] = session.isReadOnly() ? "TRUE"
                                          : "FALSE";

            t.insertSys(row);

            row = t.getEmptyRowData();
            row[0] = "DATABASE_READONLY";
            row[1] = database.databaseReadOnly ? "TRUE"
                                               : "FALSE";

            t.insertSys(row);

            // fredt - value set by SET MAXROWS _in SQL, not Statement.setMaxRows()
            row = t.getEmptyRowData();
            row[0] = "MAXROWS";
            row[1] = (session.getSQLMaxRows());

            t.insertSys(row);

            row = t.getEmptyRowData();
            row[0] = "DATABASE";
            row[1] = database.getURI();

            t.insertSys(row);

            row = t.getEmptyRowData();
            row[0] = "IDENTITY";
            row[1] = (session.getLastIdentity());

            t.insertSys(row);

            row = t.getEmptyRowData();
            row[0] = "SCHEMA";
            row[1] = (session.getSchemaName(null));

            t.insertSys(row);
            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the capabilities
         * and operating parameter properties for the engine hosting this
         * database, as well as their applicability _in terms of scope and
         * name space. <p>
         *
         * Reported properties include certain predefined <code>Database</code>
         * properties file values as well as certain database scope
         * attributes. <p>
         *
         * It is intended that all <code>Database</code> attributes and
         * properties that can be set via the database properties file,
         * JDBC connection properties or SQL SET/ALTER statements will
         * eventually be reported here or, where more applicable, _in an
         * ANSI/ISO conforming feature info base table _in the defintion
         * schema. <p>
         *
         * Currently, the database properties reported are: <p>
         *
         * <OL>
         *     <LI>hsqldb.cache_file_scale - the scaling factor used to translate data and index structure file pointers
         *     <LI>hsqldb.cache_scale - base-2 exponent scaling allowable cache row count
         *     <LI>hsqldb.cache_size_scale - base-2 exponent scaling allowable cache byte count
         *     <LI>hsqldb.cache_version -
         *     <LI>hsqldb.catalogs - whether to report the database catalog (database uri)
         *     <LI>hsqldb.compatible_version -
         *     <LI>hsqldb.files_readonly - whether the database is _in files_readonly mode
         *     <LI>hsqldb.gc_interval - # new records forcing gc ({0|NULL}=>never)
         *     <LI>hsqldb.max_nio_scale - scale factor for cache nio mapped buffers
         *     <LI>hsqldb.nio_data_file - whether cache uses nio mapped buffers
         *     <LI>hsqldb.original_version -
         *     <LI>sql.enforce_strict_size - column length specifications enforced strictly (raise exception on overflow)?
         *     <LI>textdb.all_quoted - default policy regarding whether to quote all character field values
         *     <LI>textdb.cache_scale - base-2 exponent scaling allowable cache row count
         *     <LI>textdb.cache_size_scale - base-2 exponent scaling allowable cache byte count
         *     <LI>textdb.encoding - default TEXT table file encoding
         *     <LI>textdb.fs - default field separator
         *     <LI>textdb.vs - default varchar field separator
         *     <LI>textdb.lvs - default long varchar field separator
         *     <LI>textdb.ignore_first - default policy regarding whether to ignore the first line
         *     <LI>textdb.quoted - default policy regarding treatement character field values that _may_ require quoting
         *     <LI>IGNORECASE - create table VARCHAR_IGNORECASE?
         *     <LI>LOGSIZSE - # bytes to which REDO log grows before auto-checkpoint
         *     <LI>REFERENTIAL_INTEGITY - currently enforcing referential integrity?
         *     <LI>SCRIPTFORMAT - 0 : TEXT, 1 : BINARY, ...
         *     <LI>WRITEDELAY - does REDO log currently use buffered write strategy?
         * </OL> <p>
         *
         * @return table describing database and session operating parameters
         *      and capabilities
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_PROPERTIES()
        {

            Table t = sysTables[_SYSTEM_PROPERTIES];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_PROPERTIES]);

                addColumn(t, "PROPERTY_SCOPE", Types.VARCHAR, false);
                addColumn(t, "PROPERTY_NAMESPACE", Types.VARCHAR, false);
                addColumn(t, "PROPERTY_NAME", Types.VARCHAR, false);
                addColumn(t, "PROPERTY_VALUE", Types.VARCHAR);
                addColumn(t, "PROPERTY_CLASS", Types.VARCHAR, false);

                // order PROPERTY_SCOPE, PROPERTY_NAMESPACE, PROPERTY_NAME
                // true PK
                t.createPrimaryKey(null, new int[] {
                0, 1, 2
            }, true);

                return t;
            }

            // calculated column values
            string scope;
            string nameSpace;

            // intermediate holders
            Object[] row;
            HsqlDatabaseProperties props;

            // column number mappings
            const int iscope = 0;
            const int ins = 1;
            const int iname = 2;
            const int ivalue = 3;
            const int iclass = 4;

            // First, we want the names and values for
            // all JDBC capabilities constants
            scope = "SESSION";
            props = database.getProperties();
            nameSpace = "database.properties";

            // bool properties


            foreach (var oa in props.getUserDefinedPropertyData())
            {
                Object[] metaData = (Object[])oa;

                row = t.getEmptyRowData();
                row[iscope] = scope;
                row[ins] = nameSpace;
                row[iname] = metaData[HsqlDatabaseProperties.indexName];
                row[ivalue] = props.getProperty((String)row[iname]);
                row[iclass] = metaData[HsqlDatabaseProperties.indexClass];

                t.insertSys(row);
            }

            row = t.getEmptyRowData();
            row[iscope] = scope;
            row[ins] = nameSpace;
            row[iname] = "SCRIPTFORMAT";

            try
            {
                row[ivalue] =
                    ScriptWriterBase
                        .LIST_SCRIPT_FORMATS[database.logger.getScriptType()];
            }
            catch (Exception) { }

            row[iclass] = "string";

            t.insertSys(row);

            // write delay
            row = t.getEmptyRowData();
            row[iscope] = scope;
            row[ins] = nameSpace;
            row[iname] = "WRITE_DELAY";
            row[ivalue] = "" + database.logger.getWriteDelay();
            row[iclass] = "int";

            t.insertSys(row);

            // ignore case
            row = t.getEmptyRowData();
            row[iscope] = scope;
            row[ins] = nameSpace;
            row[iname] = "IGNORECASE";
            row[ivalue] = database.isIgnoreCase() ? "true"
                                                  : "false";
            row[iclass] = "bool";

            t.insertSys(row);

            // referential integrity
            row = t.getEmptyRowData();
            row[iscope] = scope;
            row[ins] = nameSpace;
            row[iname] = "REFERENTIAL_INTEGRITY";
            row[ivalue] = database.isReferentialIntegrity() ? "true"
                                                            : "false";
            row[iclass] = "bool";

            t.insertSys(row);
            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing all visible
         * sessions. ADMIN users see *all* sessions
         * while non-admin users see only their own session.<p>
         *
         * Each row is a session state description with the following columns: <p>
         *
         * <pre class="SqlCodeExample">
         * SESSION_ID         INTEGER   session identifier
         * CONNECTED          TIMESTAMP time at which session was created
         * USER_NAME          VARCHAR   db user name of current session user
         * IS_ADMIN           bool   is session user an admin user?
         * AUTOCOMMIT         bool   is session _in autocommit mode?
         * READONLY           bool   is session _in read-only mode?
         * MAXROWS            INTEGER   session's MAXROWS setting
         * LAST_IDENTITY      INTEGER   last identity value used by this session
         * TRANSACTION_SIZE   INTEGER   # of undo items _in current transaction
         * SCHEMA             VARCHAR   current schema for session
         * </pre> <p>
         *
         * @return a <code>Table</code> object describing all visible
         *      sessions
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_SESSIONS()
        {

            Table t = sysTables[_SYSTEM_SESSIONS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_SESSIONS]);

                addColumn(t, "SESSION_ID", Types.INTEGER, false);
                addColumn(t, "CONNECTED", Types.TIMESTAMP, false);
                addColumn(t, "USER_NAME", Types.VARCHAR, false);
                addColumn(t, "IS_ADMIN", Types.BOOLEAN, false);
                addColumn(t, "AUTOCOMMIT", Types.BOOLEAN, false);
                addColumn(t, "READONLY", Types.BOOLEAN, false);
                addColumn(t, "MAXROWS", Types.INTEGER, false);

                // Note: some sessions may have a NULL LAST_IDENTITY value
                addColumn(t, "LAST_IDENTITY", Types.BIGINT);
                addColumn(t, "TRANSACTION_SIZE", Types.INTEGER, false);
                addColumn(t, "SCHEMA", Types.VARCHAR, false);

                // order:  SESSION_ID
                // true primary key
                t.createPrimaryKey(null, new int[] { 0 }, true);

                return t;
            }

            // intermediate holders
            Session[] sessions;
            Session s;
            Object[] row;

            // column number mappings
            const int isid = 0;
            const int ict = 1;
            const int iuname = 2;
            const int iis_admin = 3;
            const int iautocmt = 4;
            const int ireadonly = 5;
            const int imaxrows = 6;
            const int ilast_id = 7;
            const int it_size = 8;
            const int it_schema = 9;

            // Initialisation
            sessions = ns.listVisibleSessions(session);

            // Do it.
            for (int i = 0; i < sessions.Length; i++)
            {
                s = sessions[i];
                row = t.getEmptyRowData();
                row[isid] = (s.getId());
                row[ict] = new DateTime(s.getConnectTime());
                row[iuname] = s.getUsername();
                row[iis_admin] = (s.isAdmin());
                row[iautocmt] = (s.isAutoCommit());
                row[ireadonly] = (s.isReadOnly());
                row[imaxrows] = (s.getSQLMaxRows());
                row[ilast_id] = (s.getLastIdentity());
                row[it_size] = (s.getTransactionSize());
                row[it_schema] = s.getSchemaName(null);

                t.insertSys(row);
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the accessible
         * direct super table (if any) of each accessible table defined
         * within this database. <p>
         *
         * Each row is a super table description with the following columns: <p>
         *
         * <pre class="SqlCodeExample">
         * TABLE_CAT       VARCHAR   the table's catalog
         * TABLE_SCHEM     VARCHAR   table schema
         * TABLE_NAME      VARCHAR   table name
         * SUPERTABLE_NAME VARCHAR   the direct super table's name
         * </pre> <p>
         * @return a <code>Table</code> object describing the accessible
         *        direct supertable (if any) of each accessible
         *        table defined within this database
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_SUPERTABLES()
        {

            Table t = sysTables[_SYSTEM_SUPERTABLES];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_SUPERTABLES]);

                addColumn(t, "TABLE_CAT", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEM", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, false);         // not null
                addColumn(t, "SUPERTABLE_NAME", Types.VARCHAR, false);    // not null
                t.createPrimaryKey(null);

                return t;
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the accessible
         * direct super type (if any) of each accessible user-defined type (UDT)
         * defined within this database. <p>
         *
         * Each row is a super type description with the following columns: <p>
         *
         * <pre class="SqlCodeExample">
         * TYPE_CAT        VARCHAR   the UDT's catalog
         * TYPE_SCHEM      VARCHAR   UDT's schema
         * TYPE_NAME       VARCHAR   type name of the UDT
         * SUPERTYPE_CAT   VARCHAR   the direct super type's catalog
         * SUPERTYPE_SCHEM VARCHAR   the direct super type's schema
         * SUPERTYPE_NAME  VARCHAR   the direct super type's name
         * </pre> <p>
         * @return a <code>Table</code> object describing the accessible
         *        direct supertype (if any) of each accessible
         *        user-defined type (UDT) defined within this database
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_SUPERTYPES()
        {

            Table t = sysTables[_SYSTEM_SUPERTYPES];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_SUPERTYPES]);

                addColumn(t, "TYPE_CAT", Types.VARCHAR);
                addColumn(t, "TYPE_SCHEM", Types.VARCHAR);
                addColumn(t, "TYPE_NAME", Types.VARCHAR, false);         // not null
                addColumn(t, "SUPERTYPE_CAT", Types.VARCHAR);
                addColumn(t, "SUPERTYPE_SCHEM", Types.VARCHAR);
                addColumn(t, "SUPERTYPE_NAME", Types.VARCHAR, false);    // not null
                t.createPrimaryKey(null);

                return t;
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the TEXT TABLE objects
         * defined within this database. The table contains one row for each row
         * _in the SYSTEM_TABLES table with a HSQLDB_TYPE of  TEXT . <p>
         *
         * Each row is a description of the attributes that defines its TEXT TABLE,
         * with the following columns:
         *
         * <pre class="SqlCodeExample">
         * TABLE_CAT                 VARCHAR   table's catalog name
         * TABLE_SCHEM               VARCHAR   table's simple schema name
         * TABLE_NAME                VARCHAR   table's simple name
         * DATA_SOURCE_DEFINITION    VARCHAR   the "spec" proption of the table's
         *                                     SET TABLE ... SOURCE DDL declaration
         * FILE_PATH                 VARCHAR   absolute file path.
         * FILE_ENCODING             VARCHAR   endcoding of table's text file
         * FIELD_SEPARATOR           VARCHAR   default field separator
         * VARCHAR_SEPARATOR         VARCAHR   varchar field separator
         * LONGVARCHAR_SEPARATOR     VARCHAR   longvarchar field separator
         * IS_IGNORE_FIRST           bool   ignores first line of file?
         * IS_QUOTED                 bool   fields are quoted if necessary?
         * IS_ALL_QUOTED             bool   all fields are quoted?
         * IS_DESC                   bool   read rows starting at end of file?
         * </pre> <p>
         *
         * @return a <code>Table</code> object describing the text attributes
         * of the accessible text tables defined within this database
         * @  if an error occurs while producing the table
         *
         */
        public Table SYSTEM_TEXTTABLES()
        {

            Table t = sysTables[_SYSTEM_TEXTTABLES];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_TEXTTABLES]);

                addColumn(t, "TABLE_CAT", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEM", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, false);    // not null
                addColumn(t, "DATA_SOURCE_DEFINTION", Types.VARCHAR);
                addColumn(t, "FILE_PATH", Types.VARCHAR);
                addColumn(t, "FILE_ENCODING", Types.VARCHAR);
                addColumn(t, "FIELD_SEPARATOR", Types.VARCHAR);
                addColumn(t, "VARCHAR_SEPARATOR", Types.VARCHAR);
                addColumn(t, "LONGVARCHAR_SEPARATOR", Types.VARCHAR);
                addColumn(t, "IS_IGNORE_FIRST", Types.BOOLEAN);
                addColumn(t, "IS_ALL_QUOTED", Types.BOOLEAN);
                addColumn(t, "IS_QUOTED", Types.BOOLEAN);
                addColumn(t, "IS_DESC", Types.BOOLEAN);

                // ------------------------------------------------------------
                t.createPrimaryKey();

                return t;
            }

            // intermediate holders
            IEnumerable<Table> tables;
            //Table    table;
            Object[] row;

            //        DITableInfo ti;
            TextCache tc;

            // column number mappings
            const int itable_cat = 0;
            const int itable_schem = 1;
            const int itable_name = 2;
            const int idsd = 3;
            const int ifile_path = 4;
            const int ifile_enc = 5;
            const int ifs = 6;
            const int ivfs = 7;
            const int ilvfs = 8;
            const int iif = 9;
            const int iiq = 10;
            const int iiaq = 11;
            const int iid = 12;

            // Initialization
            tables = database.schemaManager.allTablesIterator();

            // Do it.
            foreach (var table in tables)
            {


                if (!table.isText() || !isAccessibleTable(table))
                {
                    continue;
                }

                row = t.getEmptyRowData();
                row[itable_cat] = ns.getCatalogName(table);
                row[itable_schem] = table.getSchemaName();
                row[itable_name] = table.getName().name;
                row[idsd] = table.getDataSource();

                if (table.getCache() is TextCache)
                {
                    tc = (TextCache)table.getCache();
                    row[ifile_path] =
                        FileUtil.GetDefaultInstance().CanonicalOrAbsolutePath(
                            tc.getFileName());
                    row[ifile_enc] = tc.stringEncoding;
                    row[ifs] = tc.fs;
                    row[ivfs] = tc.vs;
                    row[ilvfs] = tc.lvs;
                    row[iif] = (tc.ignoreFirst);
                    row[iiq] = (tc.isQuoted);
                    row[iiaq] = (tc.isAllQuoted);
                    row[iid] = (table.isDescDataSource());
                }

                t.insertSys(row);
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the usage
         * of accessible columns _in accessible triggers defined within
         * the database. <p>
         *
         * Each column usage description has the following columns: <p>
         *
         * <pre class="SqlCodeExample">
         * TRIGGER_CAT   VARCHAR   Trigger catalog.
         * TRIGGER_SCHEM VARCHAR   Trigger schema.
         * TRIGGER_NAME  VARCHAR   Trigger name.
         * TABLE_CAT     VARCHAR   Catalog of table on which the trigger is defined.
         * TABLE_SCHEM   VARCHAR   Schema of table on which the trigger is defined.
         * TABLE_NAME    VARCHAR   Table on which the trigger is defined.
         * COLUMN_NAME   VARCHAR   Name of the column used _in the trigger.
         * COLUMN_LIST   VARCHAR   Specified _in UPDATE clause?: ("Y" | "N"}
         * COLUMN_USAGE  VARCHAR   {"NEW" | "OLD" | "IN" | "OUT" | "IN OUT"}
         * </pre> <p>
         * @return a <code>Table</code> object describing of the usage
         *        of accessible columns _in accessible triggers
         *        defined within this database
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_TRIGGERCOLUMNS()
        {

            Table t = sysTables[_SYSTEM_TRIGGERCOLUMNS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_TRIGGERCOLUMNS]);

                addColumn(t, "TRIGGER_CAT", Types.VARCHAR);
                addColumn(t, "TRIGGER_SCHEM", Types.VARCHAR);
                addColumn(t, "TRIGGER_NAME", Types.VARCHAR);
                addColumn(t, "TABLE_CAT", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEM", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR);
                addColumn(t, "COLUMN_NAME", Types.VARCHAR);
                addColumn(t, "COLUMN_LIST", Types.VARCHAR);
                addColumn(t, "COLUMN_USAGE", Types.VARCHAR);

                // order:  all columns, _in order, as each column
                // of each table may eventually be listed under various capacities
                // (when a more comprehensive trugger system is put _in place)
                // false PK, as cat and schem may be null
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 3, 4, 5, 6, 7, 8
            }, false);

                return t;
            }

            Result rs;

            // - used appends to make class file constant pool smaller
            // - saves ~ 100 bytes jar space
            rs = session.sqlExecuteDirectNoPreChecks(
                "select a.TRIGGER_CAT,a.TRIGGER_SCHEM,a.TRIGGER_NAME, "
                + "a.TABLE_CAT,a.TABLE_SCHEM,a.TABLE_NAME,b.COLUMN_NAME,'Y',"
                + "'IN' from INFORMATION_SCHEMA.SYSTEM_TRIGGERS a, "
                + "INFORMATION_SCHEMA.SYSTEM_COLUMNS b where "
                + "a.TABLE_NAME=b.TABLE_NAME and a.TABLE_SCHEM=b.TABLE_SCHEM");

            /*
                        (new StringBuilder(185)).Append("SELECT").Append(' ').Append(
                            "a.").Append("TRIGGER_CAT").Append(',').Append("a.").Append(
                            "TRIGGER_SCHEM").Append(',').Append("a.").Append(
                            "TRIGGER_NAME").Append(',').Append("a.").Append(
                            "TABLE_CAT").Append(',').Append("a.").Append(
                            "TABLE_SCHEM").Append(',').Append("a.").Append(
                            "TABLE_NAME").Append(',').Append("b.").Append(
                            "COLUMN_NAME").Append(',').Append("'Y'").Append(',').Append(
                            "'IN'").Append(' ').Append("from").Append(' ').Append(
                            "INFORMATION_SCHEMA").Append('.').Append(
                            "SYSTEM_TRIGGERS").Append(" a,").Append(
                            "INFORMATION_SCHEMA").Append('.').Append(
                            "SYSTEM_COLUMNS").Append(" b ").Append("where").Append(
                            ' ').Append("a.").Append("TABLE_NAME").Append('=').Append(
                            "b.").Append("TABLE_NAME").ToString();
            */
            t.insertSys(rs);
            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the accessible
         * triggers defined within the database. <p>
         *
         * Each row is a trigger description with the following columns: <p>
         *
         * <pre class="SqlCodeExample">
         * TRIGGER_CAT       VARCHAR   Trigger catalog.
         * TRIGGER_SCHEM     VARCHAR   Trigger Schema.
         * TRIGGER_NAME      VARCHAR   Trigger Name.
         * TRIGGER_TYPE      VARCHAR   {("BEFORE" | "AFTER") + [" EACH ROW"] }
         * TRIGGERING_EVENT  VARCHAR   {"INSERT" | "UPDATE" | "DELETE"}
         *                             (future?: "INSTEAD OF " + ("SELECT" | ...))
         * TABLE_CAT         VARCHAR   Table's catalog.
         * TABLE_SCHEM       VARCHAR   Table's schema.
         * BASE_OBJECT_TYPE  VARCHAR   "TABLE"
         *                             (future?: "VIEW" | "SCHEMA" | "DATABASE")
         * TABLE_NAME        VARCHAR   Table on which trigger is defined
         * COLUMN_NAME       VARCHAR   NULL (future?: nested table column name)
         * REFERENCING_NAMES VARCHAR   ROW, OLD, NEW, etc.
         * WHEN_CLAUSE       VARCHAR   Condition firing trigger (NULL => always)
         * STATUS            VARCHAR   {"ENABLED" | "DISABLED"}
         * DESCRIPTION       VARCHAR   typically, the trigger's DDL
         * ACTION_TYPE       VARCHAR   "CALL" (future?: embedded language name)
         * TRIGGER_BODY      VARCHAR   Statement(s) executed
         * </pre> <p>
         *
         * @return a <code>Table</code> object describing the accessible
         *    triggers defined within this database.
         * @  if an error occurs while producing the table
         */
        public Table SYSTEM_TRIGGERS()
        {

            Table t = sysTables[_SYSTEM_TRIGGERS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_TRIGGERS]);

                addColumn(t, "TRIGGER_CAT", Types.VARCHAR);
                addColumn(t, "TRIGGER_SCHEM", Types.VARCHAR);
                addColumn(t, "TRIGGER_NAME", Types.VARCHAR, false);
                addColumn(t, "TRIGGER_TYPE", Types.VARCHAR, 15, false);
                addColumn(t, "TRIGGERING_EVENT", Types.VARCHAR, 10, false);
                addColumn(t, "TABLE_CAT", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEM", Types.VARCHAR);
                addColumn(t, "BASE_OBJECT_TYPE", Types.VARCHAR, 8, false);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, false);
                addColumn(t, "COLUMN_NAME", Types.VARCHAR);
                addColumn(t, "REFERENCING_NAMES", Types.VARCHAR, false);
                addColumn(t, "WHEN_CLAUSE", Types.VARCHAR);
                addColumn(t, "STATUS", Types.VARCHAR, 8, false);
                addColumn(t, "DESCRIPTION", Types.VARCHAR, false);
                addColumn(t, "ACTION_TYPE", Types.VARCHAR, false);
                addColumn(t, "TRIGGER_BODY", Types.VARCHAR, false);

                // order: TRIGGER_TYPE, TRIGGER_SCHEM, TRIGGER_NAME
                // added for unique: TRIGGER_CAT
                // false PK, as TRIGGER_SCHEM and/or TRIGGER_CAT may be null
                t.createPrimaryKey(null, new int[] {
                3, 1, 2, 0
            }, false);

                return t;
            }

            // calculated column values
            string triggerCatalog;
            string triggerSchema;
            string triggerName;
            string triggerType;
            string triggeringEvent;
            string tableCatalog;
            string tableSchema;
            string baseObjectType;
            string tableName;
            string columnName;
            string referencingNames;
            string whenClause;
            string status;
            string description;
            string actionType;
            string triggerBody;

            // Intermediate holders
            IEnumerable<Table> tables;
            //Table           table;
            List<TriggerDef>[] vTrigs;
            List<TriggerDef> triggerList;
            TriggerDef def;
            Object[] row;

            // column number mappings
            const int itrigger_cat = 0;
            const int itrigger_schem = 1;
            const int itrigger_name = 2;
            const int itrigger_type = 3;
            const int itriggering_event = 4;
            const int itable_cat = 5;
            const int itable_schem = 6;
            const int ibase_object_type = 7;
            const int itable_name = 8;
            const int icolumn_name = 9;
            const int ireferencing_names = 10;
            const int iwhen_clause = 11;
            const int istatus = 12;
            const int idescription = 13;
            const int iaction_type = 14;
            const int itrigger_body = 15;

            // Initialization
            tables = database.schemaManager.allTablesIterator();

            // these are the only values supported, currently
            actionType = "CALL";
            baseObjectType = "TABLE";
            columnName = null;
            referencingNames = "ROW";
            whenClause = null;

            // Do it.
            foreach (var table in tables)
            {

                vTrigs = table.triggerLists;

                // faster test first
                if (vTrigs == null)
                {
                    continue;
                }

                if (!isAccessibleTable(table))
                {
                    continue;
                }

                tableCatalog = ns.getCatalogName(table);
                triggerCatalog = tableCatalog;
                tableSchema = table.getSchemaName();
                triggerSchema = tableSchema;
                tableName = table.getName().name;

                for (int i = 0; i < vTrigs.Length; i++)
                {
                    triggerList = vTrigs[i];

                    if (triggerList == null)
                    {
                        continue;
                    }

                    for (int j = 0; j < triggerList.Count; j++)
                    {
                        def = (TriggerDef)triggerList[j];

                        if (def == null)
                        {
                            continue;
                        }

                        triggerName = def.name.name;
                        description = def.getDDL().ToString();
                        status = def.valid ? "ENABLED"
                                                : "DISABLED";
                        triggerBody = def.triggerClassName;
                        triggerType = def.when;

                        if (def.forEachRow)
                        {
                            triggerType += " EACH ROW";
                        }

                        triggeringEvent = def.operation;
                        row = t.getEmptyRowData();
                        row[itrigger_cat] = triggerCatalog;
                        row[itrigger_schem] = triggerSchema;
                        row[itrigger_name] = triggerName;
                        row[itrigger_type] = triggerType;
                        row[itriggering_event] = triggeringEvent;
                        row[itable_cat] = tableCatalog;
                        row[itable_schem] = tableSchema;
                        row[ibase_object_type] = baseObjectType;
                        row[itable_name] = tableName;
                        row[icolumn_name] = columnName;
                        row[ireferencing_names] = referencingNames;
                        row[iwhen_clause] = whenClause;
                        row[istatus] = status;
                        row[idescription] = description;
                        row[iaction_type] = actionType;
                        row[itrigger_body] = triggerBody;

                        t.insertSys(row);
                    }
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the accessible
         * attributes of the accessible user-defined type (UDT) objects
         * defined within this database. <p>
         *
         * This description does not contain inherited attributes. <p>
         *
         * Each row is a user-defined type attributes description with the
         * following columns:
         *
         * <pre class="SqlCodeExample">
         * TYPE_CAT          VARCHAR   type catalog
         * TYPE_SCHEM        VARCHAR   type schema
         * TYPE_NAME         VARCHAR   type name
         * ATTR_NAME         VARCHAR   attribute name
         * DATA_TYPE         SMALLINT  attribute's SQL type from DITypes
         * ATTR_TYPE_NAME    VARCHAR   UDT: fully qualified type name
         *                            REF: fully qualified type name of target type of
         *                            the reference type.
         * ATTR_SIZE         INTEGER   column size.
         *                            char or date types => maximum number of characters;
         *                            numeric or decimal types => precision.
         * DECIMAL_DIGITS    INTEGER   # of fractional digits (scale) of number type
         * NUM_PREC_RADIX    INTEGER   Radix of number type
         * NULLABLE          INTEGER   whether NULL is allowed
         * REMARKS           VARCHAR   comment describing attribute
         * ATTR_DEF          VARCHAR   default attribute value
         * SQL_DATA_TYPE     INTEGER   expected value of SQL CLI SQL_DESC_TYPE _in the SQLDA
         * SQL_DATETIME_SUB  INTEGER   DATETIME/INTERVAL => datetime/interval subcode
         * CHAR_OCTET_LENGTH INTEGER   for char types:  max bytes _in column
         * ORDINAL_POSITION  INTEGER   index of column _in table (starting at 1)
         * IS_NULLABLE       VARCHAR   "NO" => strictly no NULL values;
         *                             "YES" => maybe NULL values;
         *                             "" => unknown.
         * SCOPE_CATALOG     VARCHAR   catalog of REF attribute scope table or NULL
         * SCOPE_SCHEMA      VARCHAR   schema of REF attribute scope table or NULL
         * SCOPE_TABLE       VARCHAR   name of REF attribute scope table or NULL
         * SOURCE_DATA_TYPE  SMALLINT  For DISTINCT or user-generated REF DATA_TYPE:
         *                            source SQL type from DITypes
         *                            For other DATA_TYPE values:  NULL
         * </pre>
         *
         * <B>Note:</B> Currently, neither the HSQLDB engine or the JDBC driver
         * support UDTs, so an empty table is returned. <p>
         * @return a <code>Table</code> object describing the accessible
         *        attrubutes of the accessible user-defined type
         *        (UDT) objects defined within this database
         * @  if an error occurs while producing the table
         */
        public  Table SYSTEM_UDTATTRIBUTES()
        {

            Table t = sysTables[_SYSTEM_UDTATTRIBUTES];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_UDTATTRIBUTES]);

                addColumn(t, "TYPE_CAT", Types.VARCHAR);
                addColumn(t, "TYPE_SCHEM", Types.VARCHAR);
                addColumn(t, "TYPE_NAME", Types.VARCHAR, false);           // not null
                addColumn(t, "ATTR_NAME", Types.VARCHAR, false);           // not null
                addColumn(t, "DATA_TYPE", Types.SMALLINT, false);          // not null
                addColumn(t, "ATTR_TYPE_NAME", Types.VARCHAR, false);      // not null
                addColumn(t, "ATTR_SIZE", Types.INTEGER);
                addColumn(t, "DECIMAL_DIGITS", Types.INTEGER);
                addColumn(t, "NUM_PREC_RADIX", Types.INTEGER);
                addColumn(t, "NULLABLE", Types.INTEGER);
                addColumn(t, "REMARKS", Types.VARCHAR);
                addColumn(t, "ATTR_DEF", Types.VARCHAR);
                addColumn(t, "SQL_DATA_TYPE", Types.INTEGER);
                addColumn(t, "SQL_DATETIME_SUB", Types.INTEGER);
                addColumn(t, "CHAR_OCTET_LENGTH", Types.INTEGER);
                addColumn(t, "ORDINAL_POSITION", Types.INTEGER, false);    // not null
                addColumn(t, "IS_NULLABLE", Types.VARCHAR, false);         // not null
                addColumn(t, "SCOPE_CATALOG", Types.VARCHAR);
                addColumn(t, "SCOPE_SCHEMA", Types.VARCHAR);
                addColumn(t, "SCOPE_TABLE", Types.VARCHAR);
                addColumn(t, "SOURCE_DATA_TYPE", Types.SMALLINT);
                t.createPrimaryKey(null);

                return t;
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the accessible
         * user-defined types defined _in this database. <p>
         *
         * Schema-specific UDTs may have type JAVA_OBJECT, STRUCT, or DISTINCT.
         *
         * <P>Each row is a UDT descripion with the following columns:
         * <OL>
         *   <LI><B>TYPE_CAT</B> <code>VARCHAR</code> => the type's catalog
         *   <LI><B>TYPE_SCHEM</B> <code>VARCHAR</code> => type's schema
         *   <LI><B>TYPE_NAME</B> <code>VARCHAR</code> => type name
         *   <LI><B>CLASS_NAME</B> <code>VARCHAR</code> => Java class name
         *   <LI><B>DATA_TYPE</B> <code>VARCHAR</code> =>
         *         type value defined _in <code>DITypes</code>;
         *         one of <code>JAVA_OBJECT</code>, <code>STRUCT</code>, or
         *        <code>DISTINCT</code>
         *   <LI><B>REMARKS</B> <code>VARCHAR</code> =>
         *          explanatory comment on the type
         *   <LI><B>BASE_TYPE</B><code>SMALLINT</code> =>
         *          type code of the source type of a DISTINCT type or the
         *          type that : the user-generated reference type of the
         *          SELF_REFERENCING_COLUMN of a structured type as defined in
         *          DITypes (null if DATA_TYPE is not DISTINCT or not
         *          STRUCT with REFERENCE_GENERATION = USER_DEFINED)
         *
         * </OL> <p>
         *
         * <B>Note:</B> Currently, neither the HSQLDB engine or the JDBC driver
         * support UDTs, so an empty table is returned. <p>
         *
         * @return a <code>Table</code> object describing the accessible
         *      user-defined types defined _in this database
         * @  if an error occurs while producing the table
         */
        public  Table SYSTEM_UDTS()
        {

            Table t = sysTables[_SYSTEM_UDTS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_UDTS]);

                addColumn(t, "TYPE_CAT", Types.VARCHAR);
                addColumn(t, "TYPE_SCHEM", Types.VARCHAR);
                addColumn(t, "TYPE_NAME", Types.VARCHAR, false);     // not null
                addColumn(t, "CLASS_NAME", Types.VARCHAR, false);    // not null
                addColumn(t, "DATA_TYPE", Types.VARCHAR, false);     // not null
                addColumn(t, "REMARKS", Types.VARCHAR);
                addColumn(t, "BASE_TYPE", Types.SMALLINT);
                t.createPrimaryKey(null);

                return t;
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the accessible
         * columns that are automatically updated when any value _in a row
         * is updated. <p>
         *
         * Each row is a version column description with the following columns: <p>
         *
         * <OL>
         * <LI><B>SCOPE</B> <code>SMALLINT</code> => is not used
         * <LI><B>COLUMN_NAME</B> <code>VARCHAR</code> => column name
         * <LI><B>DATA_TYPE</B> <code>SMALLINT</code> =>
         *        SQL data type from java.sql.Types
         * <LI><B>TYPE_NAME</B> <code>SMALLINT</code> =>
         *       Data source dependent type name
         * <LI><B>COLUMN_SIZE</B> <code>INTEGER</code> => precision
         * <LI><B>BUFFER_LENGTH</B> <code>INTEGER</code> =>
         *        length of column value _in bytes
         * <LI><B>DECIMAL_DIGITS</B> <code>SMALLINT</code> => scale
         * <LI><B>PSEUDO_COLUMN</B> <code>SMALLINT</code> =>
         *        is this a pseudo column like an Oracle <code>ROWID</code>:<BR>
         *        (as defined _in <code>java.sql.DatabaseMetadata</code>)
         * <UL>
         *    <LI><code>versionColumnUnknown</code> - may or may not be
         *        pseudo column
         *    <LI><code>versionColumnNotPseudo</code> - is NOT a pseudo column
         *    <LI><code>versionColumnPseudo</code> - is a pseudo column
         * </UL>
         * </OL> <p>
         *
         * <B>Note:</B> Currently, the HSQLDB engine does not support version
         * columns, so an empty table is returned. <p>
         *
         * @return a <code>Table</code> object describing the columns
         *        that are automatically updated when any value
         *        _in a row is updated
         * @  if an error occurs while producing the table
         */
        public   Table SYSTEM_VERSIONCOLUMNS()
        {

            Table t = sysTables[_SYSTEM_VERSIONCOLUMNS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_VERSIONCOLUMNS]);

                // ----------------------------------------------------------------
                // required by DatabaseMetaData.getVersionColumns result set
                // ----------------------------------------------------------------
                addColumn(t, "SCOPE", Types.INTEGER);
                addColumn(t, "COLUMN_NAME", Types.VARCHAR, false);       // not null
                addColumn(t, "DATA_TYPE", Types.SMALLINT, false);        // not null
                addColumn(t, "TYPE_NAME", Types.VARCHAR, false);         // not null
                addColumn(t, "COLUMN_SIZE", Types.SMALLINT);
                addColumn(t, "BUFFER_LENGTH", Types.INTEGER);
                addColumn(t, "DECIMAL_DIGITS", Types.SMALLINT);
                addColumn(t, "PSEUDO_COLUMN", Types.SMALLINT, false);    // not null

                // -----------------------------------------------------------------
                // required by DatabaseMetaData.getVersionColumns filter parameters
                // -----------------------------------------------------------------
                addColumn(t, "TABLE_CAT", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEM", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, false);        // not null

                // -----------------------------------------------------------------
                t.createPrimaryKey(null);

                return t;
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the VIEW objects
         * defined within this database. The table contains one row for each row
         * _in the SYSTEM_TABLES table with a TABLE_TYPE of  VIEW . <p>
         *
         * Each row is a description of the query expression that defines its view,
         * with the following columns:
         *
         * <pre class="SqlCodeExample">
         * TABLE_CATALOG    VARCHAR     name of view's defining catalog.
         * TABLE_SCHEMA     VARCHAR     unqualified name of view's defining schema.
         * TABLE_NAME       VARCHAR     the simple name of the view.
         * VIEW_DEFINITION  VARCHAR     the character representation of the
         *                              &lt;query expression&gt; contained _in the
         *                              corresponding &lt;view descriptor&gt;.
         * CHECK_OPTION     VARCHAR     {"CASCADED" | "LOCAL" | "NONE"}
         * IS_UPDATABLE     VARCHAR     {"YES" | "NO"}
         * VALID            bool     Always TRUE: VIEW_DEFINITION currently
         *                              represents a valid &lt;query expression&gt.
         *
         * </pre> <p>
         *
         * @return a tabular description of the text source of all
         *        <code>View</code> objects accessible to
         *        the user.
         * @  if an error occurs while producing the table
         */
        public  Table SYSTEM_VIEWS()
        {

            Table t = sysTables[_SYSTEM_VIEWS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_VIEWS]);

                addColumn(t, "TABLE_CATALOG", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEMA", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, true);         // not null
                addColumn(t, "VIEW_DEFINITION", Types.VARCHAR, true);    // not null
                addColumn(t, "CHECK_OPTION", Types.VARCHAR, 8, true);    // not null
                addColumn(t, "IS_UPDATABLE", Types.VARCHAR, 3, true);    // not null
                addColumn(t, "VALID", Types.BOOLEAN, true);              // not null

                // order TABLE_NAME
                // added for unique: TABLE_SCHEMA, TABLE_CATALOG
                // false PK, as TABLE_SCHEMA and/or TABLE_CATALOG may be null
                t.createPrimaryKey(null, new int[] {
                1, 2, 0
            }, false);

                return t;
            }

            string defn;
            IEnumerable<Table> tables;
            //Table     table;
            Object[] row;
            const int icat = 0;
            const int ischem = 1;
            const int iname = 2;
            const int idefn = 3;
            const int icopt = 4;
            const int iiupd = 5;
            const int ivalid = 6;

            tables = database.schemaManager.allTablesIterator();

            foreach (var table in tables)
            {


                if (!table.isView() || !isAccessibleTable(table))
                {
                    continue;
                }

                row = t.getEmptyRowData();
                defn = ((View)table).getStatement();
                row[icat] = ns.getCatalogName(table);
                row[ischem] = table.getSchemaName();
                row[iname] = table.getName().name;
                row[idefn] = defn;
                row[icopt] = "NONE";
                row[iiupd] = "NO";
                row[ivalid] = true;

                t.insertSys(row);
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the
         * return, parameter and result columns of the accessible
         * routines defined within this database.<p>
         *
         * Each row is a procedure column description with the following
         * columns: <p>
         *
         * <pre class="SqlCodeExample">
         * PROCEDURE_CAT   VARCHAR   routine catalog
         * PROCEDURE_SCHEM VARCHAR   routine schema
         * PROCEDURE_NAME  VARCHAR   routine name
         * COLUMN_NAME     VARCHAR   column/parameter name
         * COLUMN_TYPE     SMALLINT  kind of column/parameter
         * DATA_TYPE       SMALLINT  SQL type from DITypes
         * TYPE_NAME       VARCHAR   SQL type name
         * PRECISION       INTEGER   precision (length) of type
         * LENGTH          INTEGER   transfer size, _in bytes, if definitely known
         *                           (roughly equivalent to BUFFER_SIZE for table
         *                           columns)
         * SCALE           SMALLINT  scale
         * RADIX           SMALLINT  radix
         * NULLABLE        SMALLINT  can column contain NULL?
         * REMARKS         VARCHAR   explanatory comment on column
         * SPECIFIC_NAME   VARCHAR   typically (but not restricted to) a
         *                           fully qulified Java Method name and signature
         * SEQ             INTEGER   The JDBC-specified order within
         *                           runs of PROCEDURE_SCHEM, PROCEDURE_NAME,
         *                           SPECIFIC_NAME, which is:
         *
         *                           return value (0), if any, first, followed
         *                           by the parameter descriptions _in call order
         *                           (1..n1), followed by the result column
         *                           descriptions _in column number order
         *                           (n1 + 1..n1 + n2)
         * </pre> <p>
         *
         * @return a <code>Table</code> object describing the
         *        return, parameter and result columns
         *        of the accessible routines defined
         *        within this database.
         * @  if an error occurs while producing the table
         */
        public override Table SYSTEM_PROCEDURECOLUMNS()
        {

            Table t = sysTables[_SYSTEM_PROCEDURECOLUMNS];

            if (t == null)
            {
                return base.SYSTEM_PROCEDURECOLUMNS();
            }

            // calculated column values
            string procedureCatalog;
            string procedureSchema;
            string procedureName;
            string columnName;
            int columnType;
            int dataType;
            string dataTypeName;
            int? precision;
            int? length;
            int? scale;
            int? radix;
            int nullability;
            string remark;
            string specificName;
            int colSequence;
            int colCount;

            // intermediate holders
            List<string> aliasList;
            Object[] info;
            MethodInfo method;
            IEnumerable<object[]> methods;         
            DITypeInfo ti;

            // Initialization
            methods = ns.iterateAllAccessibleMethods(session, true);    // and aliases
            ti = new DITypeInfo();

            // no such thing as identity or ignorecase return/parameter
            // procedure columns.  Future: may need to worry about this if
            // result columns are ever reported
            ti.setTypeSub(Types.TYPE_SUB_DEFAULT);

            // Do it.
            foreach (var temp in methods)
            {
                info = temp;
                method = (MethodInfo)info[0];
                aliasList = (List<string>)info[1];
                procedureCatalog = ns.getCatalogName(method);
                procedureSchema = ns.getSchemaName(method);

                pi.setMethod(method);

                specificName = pi.getSpecificName();
                procedureName = pi.getFQN();
                colCount = pi.getColCount();

                for (int i = 0; i < colCount; i++)
                {
                    ti.setTypeCode(pi.getColTypeCode(i));

                    columnName = pi.getColName(i);
                    columnType = pi.getColUsage(i);
                    dataType = pi.getColDataType(i);
                    dataTypeName = ti.getTypeName();
                    precision = ti.getPrecision();
                    length = pi.getColLen(i);
                    scale =ti.getDefaultScale();
                    radix = ti.getNumPrecRadix();
                    nullability = pi.getColNullability(i);
                    remark = pi.getColRemark(i);
                    colSequence = pi.getColSequence(i);

                    addPColRows(t, aliasList, procedureCatalog, procedureSchema,
                                procedureName, columnName, columnType, dataType,
                                dataTypeName, precision, length, scale, radix,
                                nullability, remark, specificName, colSequence);
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Retrieves a <code>Table</code> object describing the accessible
         * routines defined within this database.
         *
         * Each row is a procedure description with the following
         * columns: <p>
         *
         * <pre class="SqlCodeExample">
         * PROCEDURE_CAT     VARCHAR   catalog _in which routine is defined
         * PROCEDURE_SCHEM   VARCHAR   schema _in which routine is defined
         * PROCEDURE_NAME    VARCHAR   simple routine identifier
         * NUM_INPUT_PARAMS  INTEGER   number of input parameters
         * NUM_OUTPUT_PARAMS INTEGER   number of output parameters
         * NUM_RESULT_SETS   INTEGER   number of result sets returned
         * REMARKS           VARCHAR   explanatory comment on the routine
         * PROCEDURE_TYPE    SMALLINT  { Unknown | No Result | Returns Result }
         * ORIGIN            VARCHAR   {ALIAS |
         *                             [BUILTIN | USER DEFINED] ROUTINE |
         *                             [BUILTIN | USER DEFINED] TRIGGER |
         *                              ...}
         * SPECIFIC_NAME     VARCHAR   typically (but not restricted to) a
         *                             a fully qualified Java Method name
         *                             and signature
         * </pre> <p>
         *
         * @return a <code>Table</code> object describing the accessible
         *        routines defined within the this database
         * @  if an error occurs while producing the table
         */
        public override Table SYSTEM_PROCEDURES()
        {

            Table t = sysTables[_SYSTEM_PROCEDURES];

            if (t == null)
            {
                return base.SYSTEM_PROCEDURES();
            }

            // calculated column values
            // ------------------------
            // required
            // ------------------------
            string catalog;
            string schema;
            string procName;
            int numInputParams;
            int numOutputParams;
            int numResultSets;
            string remarks;
            int procRType;

            // -------------------
            // extended
            // -------------------
            string procOrigin;
            string specificName;

            // intermediate holders
            //string alias;
            List<string> aliasList;
            IEnumerable<object[]> methods;
            Object[] methodInfo;
            MethodInfo method;
            string methodOrigin;
            //Object[] row;

            // Initialization
            methods = ns.iterateAllAccessibleMethods(session, true);    //and aliases

           
           
                // Do it.
            foreach (var temp in methods)
            {
                methodInfo = (Object[])temp;
                method = (MethodInfo)methodInfo[0];
                aliasList = (List<string>)methodInfo[1];
                methodOrigin = (String)methodInfo[2];

                pi.setMethod(method);

                catalog = ns.getCatalogName(method);
                schema = ns.getSchemaName(method);
                procName = pi.getFQN();
                numInputParams = pi.getInputParmCount();
                numOutputParams = pi.getOutputParmCount();
                numResultSets = pi.getResultSetCount();
                remarks = pi.getRemark();
                procRType = pi.getResultType(methodOrigin);
                procOrigin = pi.getOrigin(methodOrigin);
                specificName = pi.getSpecificName();

                addProcRows(t, aliasList, catalog, schema, procName,
                            numInputParams, numOutputParams, numResultSets,
                            remarks, procRType, procOrigin, specificName);
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * The SYSTEM_USAGE_PRIVILEGES table has one row for each usage privilege
         * descriptor. <p>
         *
         * It effectively contains a representation of the usage privilege
         * descriptors. <p>
         *
         * <b>Definition:</b> <p>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE SYSTEM_USAGE_PRIVILEGES (
         *      GRANTOR         VARCHAR NOT NULL,
         *      GRANTEE         VARCHAR NOT NULL,
         *      OBJECT_CATALOG  VARCHAR NULL,
         *      OBJECT_SCHEMA   VARCHAR NULL,
         *      OBJECT_NAME     VARCHAR NOT NULL,
         *      OBJECT_TYPE     VARCHAR NOT NULL
         *
         *          CHECK ( OBJECT_TYPE IN (
         *                      'DOMAIN',
         *                      'CHARACTER SET',
         *                      'COLLATION',
         *                      'TRANSLATION',
         *                      'SEQUENCE' ) ),
         *
         *      IS_GRANTABLE    VARCHAR NOT NULL
         *
         *          CHECK ( IS_GRANTABLE IN ( 'YES', 'NO' ) ),
         *
         *      UNIQUE( GRANTOR, GRANTEE, OBJECT_CATALOG,
         *              OBJECT_SCHEMA, OBJECT_NAME, OBJECT_TYPE )
         * )
         * </pre>
         *
         * <b>Description:</b><p>
         *
         * <ol>
         * <li> The value of GRANTOR is the &lt;authorization identifier&gt; of the
         *      user or role who granted usage privileges on the object of the type
         *      identified by OBJECT_TYPE that is identified by OBJECT_CATALOG,
         *      OBJECT_SCHEMA, and OBJECT_NAME, to the user or role identified by the
         *      value of GRANTEE forthe usage privilege being described. <p>
         *
         * <li> The value of GRANTEE is the &lt;authorization identifier&gt; of some
         *      user or role, or PUBLIC to indicate all users, to whom the usage
         *      privilege being described is granted. <p>
         *
         * <li> The values of OBJECT_CATALOG, OBJECT_SCHEMA, and OBJECT_NAME are the
         *      catalog name, unqualified schema name, and qualified identifier,
         *      respectively, of the object to which the privilege applies. <p>
         *
         * <li> The values of OBJECT_TYPE have the following meanings: <p>
         *
         *      <table border cellpadding="3">
         *          <tr>
         *              <td nowrap>DOMAIN</td>
         *              <td nowrap>The object to which the privilege applies is
         *                         a domain.</td>
         *          <tr>
         *          <tr>
         *              <td nowrap>CHARACTER SET</td>
         *              <td nowrap>The object to which the privilege applies is a
         *                         character set.</td>
         *          <tr>
         *          <tr>
         *              <td nowrap>COLLATION</td>
         *              <td nowrap>The object to which the privilege applies is a
         *                         collation.</td>
         *          <tr>
         *          <tr>
         *              <td nowrap>TRANSLATION</td>
         *              <td nowrap>The object to which the privilege applies is a
         *                         transliteration.</td>
         *          <tr>
         *          <tr>
         *              <td nowrap>SEQUENCE</td>
         *              <td nowrap>The object to which the privilege applies is a
         *                         sequence generator.</td>
         *          <tr>
         *      </table> <p>
         *
         * <li> The values of IS_GRANTABLE have the following meanings: <p>
         *
         *      <table border cellpadding="3">
         *          <tr>
         *              <td nowrap>YES</td>
         *              <td nowrap>The privilege being described was granted
         *                         WITH GRANT OPTION and is thus grantable.</td>
         *          <tr>
         *          <tr>
         *              <td nowrap>NO</td>
         *              <td nowrap>The privilege being described was not granted
         *                  WITH GRANT OPTION and is thus not grantable.</td>
         *          <tr>
         *      </table> <p>
         * <ol>
         */
        public Table SYSTEM_USAGE_PRIVILEGES()
        {

            Table t = sysTables[_SYSTEM_USAGE_PRIVILEGES];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_USAGE_PRIVILEGES]);

                addColumn(t, "GRANTOR", Types.VARCHAR, false);            // not null
                addColumn(t, "GRANTEE", Types.VARCHAR, false);            // not null
                addColumn(t, "OBJECT_CATALOG", Types.VARCHAR);
                addColumn(t, "OBJECT_SCHEMA", Types.VARCHAR);
                addColumn(t, "OBJECT_NAME", Types.VARCHAR, false);        // not null
                addColumn(t, "OBJECT_TYPE", Types.VARCHAR, 32, false);    // not null
                addColumn(t, "IS_GRANTABLE", Types.VARCHAR, 3, false);    // not null

                // order: COLUMN_NAME, PRIVILEGE
                // for unique: GRANTEE, GRANTOR, TABLE_NAME, TABLE_SCHEM, TABLE_CAT
                // false PK, as TABLE_SCHEM and/or TABLE_CAT may be null
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 3, 4, 5
            }, false);

                return t;
            }

            Result rs;

            rs = session.sqlExecuteDirectNoPreChecks(
                "SELECT '" + GranteeManager.SYSTEM_AUTHORIZATION_NAME
                + "', 'PUBLIC', SEQUENCE_CATALOG, SEQUENCE_SCHEMA, "
                + "SEQUENCE_NAME, 'SEQUENCE', 'FALSE' FROM  INFORMATION_SCHEMA.SYSTEM_SEQUENCES");

            t.insertSys(rs);

            rs = session.sqlExecuteDirectNoPreChecks(
                "SELECT '" + GranteeManager.SYSTEM_AUTHORIZATION_NAME
                + "', 'PUBLIC', COLLATION_CATALOG, COLLATION_SCHEMA, "
                + "COLLATION_NAME, 'COLLATION', 'FALSE' FROM  INFORMATION_SCHEMA.SYSTEM_COLLATIONS");

            t.insertSys(rs);
            t.setDataReadOnly(true);

            return t;
        }

        /**
         * The CHECK_COLUMN_USAGE table has one row for each column identified by
         * a &lt;column reference&gt; contained _in the &lt;search condition&gt;
         * of a check constraint, domain constraint, or assertion. <p>
         *
         * <b>Definition:</b><p>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE CHECK_COLUMN_USAGE (
         *      CONSTRAINT_CATALOG  VARCHAR NULL,
         *      CONSTRAINT_SCHEMA   VARCHAR NULL,
         *      CONSTRAINT_NAME     VARCHAR NOT NULL,
         *      TABLE_CATALOG       VARCHAR NULL,
         *      TABLE_SCHEMA        VARCHAR NULL,
         *      TABLE_NAME          VARCHAR NOT NULL,
         *      COLUMN_NAME         VARCHAR NOT NULL,
         *      UNIQUE( CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME,
         *              TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME )
         * )
         * </pre>
         *
         * <b>Description:</b> <p>
         *
         * <ol>
         * <li> The values of CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, and
         *      CONSTRAINT_NAME are the catalog name, unqualified schema name,
         *      and qualified identifier, respectively, of the constraint being
         *      described. <p>
         *
         * <li> The values of TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and
         *      COLUMN_NAME are the catalog name, unqualified schema name,
         *      qualified identifier, and column name, respectively, of a column
         *      identified by a &lt;column reference&gt; explicitly or implicitly
         *      contained _in the &lt;search condition&gt; of the constraint
         *      being described.
         * </ol>
         */
        public Table SYSTEM_CHECK_COLUMN_USAGE()
        {

            Table t = sysTables[_SYSTEM_CHECK_COLUMN_USAGE];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_CHECK_COLUMN_USAGE]);

                addColumn(t, "CONSTRAINT_CATALOG", Types.VARCHAR);
                addColumn(t, "CONSTRAINT_SCHEMA", Types.VARCHAR);
                addColumn(t, "CONSTRAINT_NAME", Types.VARCHAR, false);    // not null
                addColumn(t, "TABLE_CATALOG", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEMA", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, false);         // not null
                addColumn(t, "COLUMN_NAME", Types.VARCHAR, false);        // not null
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 3, 4, 5, 6
            }, false);

                return t;
            }

            // calculated column values
            string constraintCatalog;
            string constraintSchema;
            string constraintName;

            // Intermediate holders
            IEnumerable<Table> tables;
            //Table                table;
            Constraint[] constraints;
            int constraintCount;
            Constraint constraint;
            Expression.Collector collector;
            Expression expression;
            TableFilter tableFilter;
            Table columnTable;
            IEnumerable<Expression> iterator;
            Result result;
            Object[] resultRow;
            Object[] row;

            // column number mappings
            const int icons_cat = 0;
            const int icons_schem = 1;
            const int icons_name = 2;
            const int itab_cat = 3;
            const int itab_schem = 4;
            const int itab_name = 5;
            const int itab_col = 6;

            // Initialization
            tables = database.schemaManager.allTablesIterator();
            collector = new Expression.Collector();
            result = new Result(ResultConstants.DATA, 4);
            result.metaData.colTypes[0] = result.metaData.colTypes[1] =
                result.metaData.colTypes[2] = result.metaData.colTypes[3] =
                Types.VARCHAR;

            // Do it.
            foreach (var table in tables)
            {

                if (!isAccessibleTable(table))
                {
                    continue;
                }

                constraints = table.getConstraints();
                constraintCount = constraints.Length;
                constraintCatalog = ns.getCatalogName(table);
                constraintSchema = table.getSchemaName();

                // process constraints
                for (int i = 0; i < constraintCount; i++)
                {
                    constraint = (Constraint)constraints[i];

                    if (constraint.getType() != Constraint.CHECK)
                    {
                        continue;
                    }

                    constraintName = constraint.getName().name;

                    result.setRows(null);
                    collector.Clear();
                    collector.addAll(constraint.core.check, Expression.COLUMN);

                    iterator = collector;

                    // calculate distinct column references
                    foreach (var exp in iterator)
                    {
                        expression = (Expression)exp;
                        tableFilter = expression.getFilter();
                        columnTable = tableFilter.getTable();

                        if (columnTable.getTableType() == Table.SYSTEM_SUBQUERY
                                || !isAccessibleTable(columnTable))
                        {
                            continue;
                        }

                        result.add(new Object[] {
                        ns.getCatalogName(columnTable),
                        columnTable.getSchemaName(),
                        columnTable.getName().name, expression.getColumnName()
                    });
                    }

                    /*
                                    result.removeDuplicates(
                                        database.sessionManager.getSysSession(
                                            database.schemaManager.INFORMATION_SCHEMA));
                    */
                    result.removeDuplicates(session);

                    Iterator iter = result.iterator();

                    while (iter.hasNext())
                    {
                        row = t.getEmptyRowData();
                        resultRow = (Object[])iter.next();
                        row[icons_cat] = constraintCatalog;
                        row[icons_schem] = constraintSchema;
                        row[icons_name] = constraintName;
                        row[itab_cat] = resultRow[0];
                        row[itab_schem] = resultRow[1];
                        row[itab_name] = resultRow[2];
                        row[itab_col] = resultRow[3];

                        t.insertSys(row);
                    }
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * The CHECK_ROUTINE_USAGE base table has one row for each
         * SQL-invoked routine identified as the subject routine of either a
         * &lt;routine invocation&gt;, a &lt;method reference&gt;, a
         * &lt;method invocation&gt;, or a &lt;static method invocation&gt;
         * contained _in an &lt;assertion definition&gt;, a &lt;domain
         * constraint&gt;, or a &lt;table constraint definition&gt;. <p>
         *
         * <b>Definition:</b> <p>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE SYSTEM_CHECK_ROUTINE_USAGE (
         *      CONSTRAINT_CATALOG      VARCHAR NULL,
         *      CONSTRAINT_SCHEMA       VARCHAR NULL,
         *      CONSTRAINT_NAME         VARCHAR NOT NULL,
         *      SPECIFIC_CATALOG        VARCHAR NULL,
         *      SPECIFIC_SCHEMA         VARCHAR NULL,
         *      SPECIFIC_NAME           VARCHAR NOT NULL,
         *      UNIQUE( CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME,
         *              SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME )
         * )
         * </pre>
         *
         * <b>Description:</b> <p>
         *
         * <ol>
         * <li> The CHECK_ROUTINE_USAGE table has one row for each
         *      SQL-invoked routine R identified as the subject routine of either a
         *      &lt;routine invocation&gt;, a &lt;method reference&gt;, a &lt;method
         *      invocation&gt;, or a &lt;static method invocation&gt; contained in
         *      an &lt;assertion definition&gt; or _in the &lt;check constraint
         *      definition&gt; contained _in either a &lt;domain constraint&gt; or a
         *      &lt;table constraint definition&gt;. <p>
         *
         * <li> The values of CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, and
         *      CONSTRAINT_NAME are the catalog name, unqualified schema name, and
         *      qualified identifier, respectively, of the assertion or check
         *     constraint being described. <p>
         *
         * <li> The values of SPECIFIC_CATALOG, SPECIFIC_SCHEMA, and SPECIFIC_NAME
         *      are the catalog name, unqualified schema name, and qualified
         *      identifier, respectively, of the specific name of R. <p>
         *
         * </ol>
         */
        public Table SYSTEM_CHECK_ROUTINE_USAGE()
        {

            Table t = sysTables[_SYSTEM_CHECK_ROUTINE_USAGE];

            if (t == null)
            {
                t = createBlankTable(
                    sysTableHsqlNames[_SYSTEM_CHECK_ROUTINE_USAGE]);

                addColumn(t, "CONSTRAINT_CATALOG", Types.VARCHAR);
                addColumn(t, "CONSTRAINT_SCHEMA", Types.VARCHAR);
                addColumn(t, "CONSTRAINT_NAME", Types.VARCHAR, false);    // not null
                addColumn(t, "SPECIFIC_CATALOG", Types.VARCHAR);
                addColumn(t, "SPECIFIC_SCHEMA", Types.VARCHAR);
                addColumn(t, "SPECIFIC_NAME", Types.VARCHAR, false);      // not null
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 3, 4, 5
            }, false);

                return t;
            }

            // calculated column values
            string constraintCatalog;
            string constraintSchema;
            string constraintName;

            // Intermediate holders
            IEnumerable<Table> tables;
            //Table                table;
            Constraint[] constraints;
            int constraintCount;
            Constraint constraint;
            Expression.Collector collector;
            Expression expression;
            Function function;
            //Iterator iterator;
            HashSet<MethodInfo> methodSet;
            MethodInfo method;
            Object[] row;

            // column number mappings
            const int icons_cat = 0;
            const int icons_schem = 1;
            const int icons_name = 2;
            const int ir_cat = 3;
            const int ir_schem = 4;
            const int ir_name = 5;

            tables = database.schemaManager.allTablesIterator();
            collector = new Expression.Collector();

            foreach (var table in tables)
            {
                collector.Clear();

                if (!isAccessibleTable(table))
                {
                    continue;
                }

                constraints = table.getConstraints();
                constraintCount = constraints.Length;
                constraintCatalog = ns.getCatalogName(table);
                constraintSchema = table.getSchemaName();

                for (int i = 0; i < constraintCount; i++)
                {
                    constraint = (Constraint)constraints[i];

                    if (constraint.getType() != Constraint.CHECK)
                    {
                        continue;
                    }

                    constraintName = constraint.getName().name;

                    collector.addAll(constraint.core.check, Expression.FUNCTION);

                    methodSet = new HashSet<MethodInfo>();


                    foreach (var expr in collector)
                    {
                        expression = (Expression)expr;
                        function = expression.function;

                        if (!session
                                .isAccessible(function.getMethod()
                                    .DeclaringType.Name))
                        {
                            continue;
                        }

                        methodSet.Add(function.getMethod());
                    }



                    foreach (var m in methodSet)
                    {
                        method = (MethodInfo)m;
                        row = t.getEmptyRowData();
                        row[icons_cat] = constraintCatalog;
                        row[icons_schem] = constraintSchema;
                        row[icons_name] = constraintName;
                        row[ir_cat] = ns.getCatalogName(method);
                        row[ir_schem] = ns.getSchemaName(method);
                        row[ir_name] = DINameSpace.getMethodSpecificName(method);

                        t.insertSys(row);
                    }
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * The CHECK_TABLE_USAGE table has one row for each table identified by a
         * &lt;table name&gt; simply contained _in a &lt;table reference&gt;
         * contained _in the &lt;search condition&gt; of a check constraint,
         * domain constraint, or assertion. <p>
         *
         * <b>Definition:</b> <p>
         *
         * <pre class="SqlCodeExample">
         * CREATE STABLE SYSTEM_CHECK_TABLE_USAGE (
         *      CONSTRAINT_CATALOG      VARCHAR NULL,
         *      CONSTRAINT_SCHEMA       VARCHAR NULL,
         *      CONSTRAINT_NAME         VARCHAR NOT NULL,
         *      TABLE_CATALOG           VARCHAR NULL,
         *      TABLE_SCHEMA            VARCHAR NOT NULL,
         *      TABLE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *     UNIQUE( CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME,
         *            TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME )
         * )
         * </pre>
         *
         * <b>Description:</b> <p>
         *
         * <ol>
         * <li> The values of CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, and
         *      CONSTRAINT_NAME are the catalog name, unqualified schema name,
         *       and qualified identifier, respectively, of the constraint being
         *      described. <p>
         *
         * <li> The values of TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME are the
         *      catalog name, unqualified schema name, and qualified identifier,
         *      respectively, of a table identified by a &lt;table name&gt;
         *      simply contained _in a &lt;table reference&gt; contained _in the
         *      *lt;search condition&gt; of the constraint being described.
         * </ol>
         */
        public Table SYSTEM_CHECK_TABLE_USAGE()
        {

            Table t = sysTables[_SYSTEM_CHECK_TABLE_USAGE];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_CHECK_TABLE_USAGE]);

                addColumn(t, "CONSTRAINT_CATALOG", Types.VARCHAR);
                addColumn(t, "CONSTRAINT_SCHEMA", Types.VARCHAR);
                addColumn(t, "CONSTRAINT_NAME", Types.VARCHAR, false);    // not null
                addColumn(t, "TABLE_CATALOG", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEMA", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, false);         // not null
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 3, 4, 5
            }, false);

                return t;
            }

            //
            Result rs = session.sqlExecuteDirectNoPreChecks(
                "select DISTINCT CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, "
                + "CONSTRAINT_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME "
                + "from INFORMATION_SCHEMA.SYSTEM_CHECK_COLUMN_USAGE");

            t.insertSys(rs);
            t.setDataReadOnly(true);

            return t;
        }

        /**
         * The TABLE_CONSTRAINTS table has one row for each table constraint
         * associated with a table.  <p>
         *
         * It effectively contains a representation of the table constraint
         * descriptors. <p>
         *
         * <b>Definition:</b> <p>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE SYSTEM_TABLE_CONSTRAINTS (
         *      CONSTRAINT_CATALOG      VARCHAR NULL,
         *      CONSTRAINT_SCHEMA       VARCHAR NULL,
         *      CONSTRAINT_NAME         VARCHAR NOT NULL,
         *      CONSTRAINT_TYPE         VARCHAR NOT NULL,
         *      TABLE_CATALOG           VARCHAR NULL,
         *      TABLE_SCHEMA            VARCHAR NULL,
         *      TABLE_NAME              VARCHAR NOT NULL,
         *      IS_DEFERRABLE           VARCHAR NOT NULL,
         *      INITIALLY_DEFERRED      VARCHAR NOT NULL,
         *
         *      CHECK ( CONSTRAINT_TYPE IN
         *                      ( 'UNIQUE', 'PRIMARY KEY',
         *                        'FOREIGN KEY', 'CHECK' ) ),
         *
         *      CHECK ( ( IS_DEFERRABLE, INITIALLY_DEFERRED ) IN
         *              ( VALUES ( 'NO',  'NO'  ),
         *                       ( 'YES', 'NO'  ),
         *                       ( 'YES', 'YES' ) ) )
         * )
         * </pre>
         *
         * <b>Description:</b> <p>
         *
         * <ol>
         * <li> The values of CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, and
         *      CONSTRAINT_NAME are the catalog name, unqualified schema
         *      name, and qualified identifier, respectively, of the
         *      constraint being described. If the &lt;table constraint
         *      definition&gt; or &lt;add table constraint definition&gt;
         *      that defined the constraint did not specify a
         *      &lt;constraint name&gt;, then the values of CONSTRAINT_CATALOG,
         *      CONSTRAINT_SCHEMA, and CONSTRAINT_NAME are
         *      implementation-defined. <p>
         *
         * <li> The values of CONSTRAINT_TYPE have the following meanings: <p>
         *  <table border cellpadding="3">
         *  <tr>
         *      <td nowrap>FOREIGN KEY</td>
         *      <td nowrap>The constraint being described is a
         *                 foreign key constraint.</td>
         *  </tr>
         *  <tr>
         *      <td nowrap>UNIQUE</td>
         *      <td nowrap>The constraint being described is a
         *                 unique constraint.</td>
         *  </tr>
         *  <tr>
         *      <td nowrap>PRIMARY KEY</td>
         *      <td nowrap>The constraint being described is a
         *                 primary key constraint.</td>
         *  </tr>
         *  <tr>
         *      <td nowrap>CHECK</td>
         *      <td nowrap>The constraint being described is a
         *                 check constraint.</td>
         *  </tr>
         * </table> <p>
         *
         * <li> The values of TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME are
         *      the catalog name, the unqualified schema name, and the
         *      qualified identifier of the name of the table to which the
         *      table constraint being described applies. <p>
         *
         * <li> The values of IS_DEFERRABLE have the following meanings: <p>
         *
         *  <table>
         *      <tr>
         *          <td nowrap>YES</td>
         *          <td nowrap>The table constraint is deferrable.</td>
         *      </tr>
         *      <tr>
         *          <td nowrap>NO</td>
         *          <td nowrap>The table constraint is not deferrable.</td>
         *      </tr>
         *  </table> <p>
         *
         * <li> The values of INITIALLY_DEFERRED have the following meanings: <p>
         *
         *  <table>
         *      <tr>
         *          <td nowrap>YES</td>
         *          <td nowrap>The table constraint is initially deferred.</td>
         *      </tr>
         *      <tr>
         *          <td nowrap>NO</td>
         *          <td nowrap>The table constraint is initially immediate.</td>
         *      </tr>
         *  </table> <p>
         * </ol>
         */
        public  Table SYSTEM_TABLE_CONSTRAINTS()
        {

            Table t = sysTables[_SYSTEM_TABLE_CONSTRAINTS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_TABLE_CONSTRAINTS]);

                addColumn(t, "CONSTRAINT_CATALOG", Types.VARCHAR);
                addColumn(t, "CONSTRAINT_SCHEMA", Types.VARCHAR);
                addColumn(t, "CONSTRAINT_NAME", Types.VARCHAR, false);          // not null
                addColumn(t, "CONSTRAINT_TYPE", Types.VARCHAR, 11, false);      // not null
                addColumn(t, "TABLE_CATALOG", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEMA", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, false);               // not null
                addColumn(t, "IS_DEFERRABLE", Types.VARCHAR, 3, false);         // not null
                addColumn(t, "INITIALLY_DEFERRED", Types.VARCHAR, 3, false);    // not null

                // false PK, as CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
                // TABLE_CATALOG and/or TABLE_SCHEMA may be null
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 4, 5, 6
            }, false);

                return t;
            }

            // Intermediate holders
            IEnumerable<Table> tables;
            //Table        table;
            Constraint[] constraints;
            int constraintCount;
            Constraint constraint;
            string cat;
            string schem;
            HashSet<Constraint> constraintSet;
            Object[] row;

            // column number mappings
            const int icons_cat = 0;
            const int icons_schem = 1;
            const int icons_name = 2;
            const int icons_type = 3;
            const int itab_cat = 4;
            const int itab_schem = 5;
            const int itab_name = 6;
            const int iis_defr = 7;
            const int iinit_defr = 8;

            // initialization
            tables = database.schemaManager.allTablesIterator();
            constraintSet = new HashSet<Constraint>();


            // do it
            foreach (var table in tables)
            {


                if (table.isView() || !isAccessibleTable(table))
                {
                    continue;
                }

                constraints = table.getConstraints();
                constraintCount = constraints.Length;

                for (int i = 0; i < constraintCount; i++)
                {
                    constraint = constraints[i];

                    if (constraint.getType() == Constraint.FOREIGN_KEY
                            && !isAccessibleTable(constraint.getRef()))
                    {
                        continue;
                    }

                    constraintSet.Add(constraint);
                }
            }

            Table temp = null;
            foreach (var c in constraintSet)
            {
                row = t.getEmptyRowData();
                constraint = (Constraint)c;

                switch (constraint.getType())
                {

                    case Constraint.CHECK:
                        {
                            row[icons_type] = "CHECK";
                            temp = constraint.getMain();

                            break;
                        }
                    case Constraint.UNIQUE:
                        {
                            row[icons_type] = "UNIQUE";
                            temp = constraint.getMain();

                            break;
                        }
                    case Constraint.FOREIGN_KEY:
                        {
                            row[icons_type] = "FOREIGN KEY";
                            temp = constraint.getRef();

                            break;
                        }
                    case Constraint.PRIMARY_KEY:
                        {
                            row[icons_type] = "PRIMARY KEY";
                            temp = constraint.getMain();

                            break;
                        }
                    case Constraint.MAIN:
                    default:
                        {
                            continue;
                        }
                }

                cat = ns.getCatalogName(temp);
                schem = temp.getSchemaName();
                row[icons_cat] = cat;
                row[icons_schem] = schem;
                row[icons_name] = constraint.constName.name;
                row[itab_cat] = cat;
                row[itab_schem] = schem;
                row[itab_name] = temp.getName().name;
                row[iis_defr] = "NO";
                row[iinit_defr] = "NO";

                t.insertSys(row);
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * The SYSTEM_VIEW_TABLE_USAGE table has one row for each table identified
         * by a &lt;table name&gt; simply contained _in a &lt;table reference&gt;
         * that is contained _in the &lt;query expression&gt; of a view. <p>
         *
         * <b>Definition</b><p>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE SYSTEM_VIEW_TABLE_USAGE (
         *      VIEW_CATALOG    VARCHAR NULL,
         *      VIEW_SCHEMA     VARCHAR NULL,
         *      VIEW_NAME       VARCHAR NULL,
         *      TABLE_CATALOG   VARCHAR NULL,
         *      TABLE_SCHEMA    VARCHAR NULL,
         *      TABLE_NAME      VARCHAR NULL,
         *      UNIQUE( VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME,
         *              TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME )
         * )
         * </pre>
         *
         * <b>Description:</b><p>
         *
         * <ol>
         * <li> The values of VIEW_CATALOG, VIEW_SCHEMA, and VIEW_NAME are the
         *      catalog name, unqualified schema name, and qualified identifier,
         *      respectively, of the view being described. <p>
         *
         * <li> The values of TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME are the
         *      catalog name, unqualified schema name, and qualified identifier,
         *      respectively, of a table identified by a &lt;table name&gt;
         *      simply contained _in a &lt;table reference&gt; that is contained in
         *      the &lt;query expression&gt; of the view being described.
         * </ol>
         */
        public Table SYSTEM_VIEW_TABLE_USAGE()
        {

            Table t = sysTables[_SYSTEM_VIEW_TABLE_USAGE];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_VIEW_TABLE_USAGE]);

                addColumn(t, "VIEW_CATALOG", Types.VARCHAR);
                addColumn(t, "VIEW_SCHEMA", Types.VARCHAR);
                addColumn(t, "VIEW_NAME", Types.VARCHAR, true);     // not null
                addColumn(t, "TABLE_CATALOG", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEMA", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, true);    // not null

                // false PK, as VIEW_CATALOG, VIEW_SCHEMA, TABLE_CATALOG, and/or
                // TABLE_SCHEMA may be NULL
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 3, 4, 5
            }, false);

                return t;
            }

            //
            Result rs = session.sqlExecuteDirectNoPreChecks(
                "select DISTINCT VIEW_CATALOG, VIEW_SCHEMA, "
                + "VIEW_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME "
                + "from INFORMATION_SCHEMA.SYSTEM_VIEW_COLUMN_USAGE");

            t.insertSys(rs);
            t.setDataReadOnly(true);

            return t;
        }

        /**
         * The SYSTEM_VIEW_COLUMN_USAGE table has one row for each column of a
         * table that is explicitly or implicitly referenced _in the
         * &lt;query expression&gt; of the view being described. <p>
         *
         * <b>Definition:</b> <p>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE SYSTEM_VIEW_COLUMN_USAGE (
         *      VIEW_CATALOG    VARCHAR NULL,
         *      VIEW_SCHEMA     VARCHAR NULL,
         *      VIEW_NAME       VARCHAR NOT NULL,
         *      TABLE_CATALOG   VARCHAR NULL,
         *      TABLE_SCHEMA    VARCHAR NULL,
         *      TABLE_NAME      VARCHAR NOT NULL,
         *      COLUMN_NAME     VARCHAR NOT NULL,
         *      UNIQUE ( VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME,
         *               TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
         *               COLUMN_NAME )
         * )
         * </pre>
         *
         * <b>Description:</b> <p>
         *
         * <ol>
         * <li> The values of VIEW_CATALOG, VIEW_SCHEMA, and VIEW_NAME are the
         *      catalog name, unqualified schema name, and qualified identifier,
         *      respectively, of the view being described. <p>
         *
         * <li> The values of TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, and
         *      COLUMN_NAME are the catalog name, unqualified schema name,
         *      qualified identifier, and column name, respectively, of a column
         *      of a table that is explicitly or implicitly referenced _in the
         *      &lt;query expression&gt; of the view being described.
         * </ol>
         */
        public Table SYSTEM_VIEW_COLUMN_USAGE()
        {

            Table t = sysTables[_SYSTEM_VIEW_COLUMN_USAGE];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_VIEW_COLUMN_USAGE]);

                addColumn(t, "VIEW_CATALOG", Types.VARCHAR);
                addColumn(t, "VIEW_SCHEMA", Types.VARCHAR);
                addColumn(t, "VIEW_NAME", Types.VARCHAR, true);      // not null
                addColumn(t, "TABLE_CATALOG", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEMA", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, true);     // not null
                addColumn(t, "COLUMN_NAME", Types.VARCHAR, true);    // not null

                // false PK, as VIEW_CATALOG, VIEW_SCHEMA, TABLE_CATALOG, and/or
                // TABLE_SCHEMA may be NULL
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 3, 4, 5, 6
            }, false);

                return t;
            }

            // Calculated column values
            string viewCatalog;
            string viewSchema;
            string viewName;

            // Intermediate holders
            IEnumerable<Table> tables;
            View view;
            Table table;
            Object[] row;
            SubQuery[] subqueries;
            //Select select;
            Expression expression;
            TableFilter tableFilter;
            Table columnTable;
            Result result;
            Object[] resultRow;
            Iterator iterator;
            Expression.Collector collector;

            // Column number mappings
            const int iv_cat = 0;
            const int iv_schem = 1;
            const int iv_name = 2;
            const int it_cat = 3;
            const int it_schem = 4;
            const int it_name = 5;
            const int it_cname = 6;

            // Initialization
            tables = database.schemaManager.allTablesIterator();
            collector = new Expression.Collector();
            result = new Result(ResultConstants.DATA, 4);
            result.metaData.colTypes[0] = result.metaData.colTypes[1] =
                result.metaData.colTypes[2] = result.metaData.colTypes[3] =
                Types.VARCHAR;

            // Do it.
            foreach (var temp in tables)
            {
                collector.Clear();
                result.setRows(null);

                table = (Table)temp;

                if (table.isView() && isAccessibleTable(table))
                {

                    // fall through
                }
                else
                {
                    continue;
                }

                viewCatalog = ns.getCatalogName(table);
                viewSchema = table.getSchemaName();
                viewName = table.getName().name;
                view = (View)table;
                subqueries = view.viewSubqueries;

                collector.addAll(view.viewSelect, Expression.COLUMN);

                for (int i = 0; i < subqueries.Length; i++)
                {
                    collector.addAll(subqueries[i].select, Expression.COLUMN);
                }



                foreach (var expr in collector)
                {
                    expression = (Expression)expr;
                    tableFilter = expression.getFilter();
                    columnTable = tableFilter.getTable();

                    if (columnTable.getTableType() == Table.SYSTEM_SUBQUERY
                            || !isAccessibleTable(columnTable))
                    {
                        continue;
                    }

                    result.add(new Object[] {
                    ns.getCatalogName(columnTable),
                    columnTable.getSchemaName(), columnTable.getName().name,
                    expression.getColumnName()
                });
                }

                /*
                            result.removeDuplicates(
                                database.sessionManager.getSysSession(
                                    database.schemaManager.INFORMATION_SCHEMA));
                */
                result.removeDuplicates(session);

                iterator = result.iterator();

                while (iterator.hasNext())
                {
                    row = t.getEmptyRowData();
                    resultRow = (Object[])iterator.next();
                    row[iv_cat] = viewCatalog;
                    row[iv_schem] = viewSchema;
                    row[iv_name] = viewName;
                    row[it_cat] = resultRow[0];
                    row[it_schem] = resultRow[1];
                    row[it_name] = resultRow[2];
                    row[it_cname] = resultRow[3];

                    t.insertSys(row);
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * The SYSTEM_VIEW_ROUTINE_USAGE table has one row for each SQL-invoked
         * routine identified as the subject routine of either a &lt;routine
         * invocation&gt;, a &lt;method reference&gt;, a &lt;method invocation&gt;,
         * or a &lt;static method invocation&gt; contained _in a &lt;view
         * definition&gt;. <p>
         *
         * <b>Definition</b><p>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE VIEW_ROUTINE_USAGE (
         *      TABLE_CATALOG       VARCHAR NULL,
         *      TABLE_SCHEMA        VARCHAR NULL,
         *      TABLE_NAME          VARCHAR NOT NULL,
         *      SPECIFIC_CATALOG    VARCHAR NULL,
         *      SPECIFIC_SCHEMA     VARCHAR NULL,
         *      SPECIFIC_NAME       VARCHAR NOT NULL,
         *      UNIQUE( TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
         *              SPECIFIC_CATALOG, SPECIFIC_SCHEMA,
         *              SPECIFIC_NAME )
         * )
         * </pre>
         *
         * <b>Description</b><p>
         *
         * <ol>
         * <li> The values of TABLE_CATALOG, TABLE_SCHEMA, and TABLE_NAME are the
         *      catalog name, unqualified schema name, and qualified identifier,
         *      respectively, of the viewed table being described. <p>
         *
         * <li> The values of SPECIFIC_CATALOG, SPECIFIC_SCHEMA, and SPECIFIC_NAME are
         *      the catalog name, unqualified schema name, and qualified identifier,
         *      respectively, of the specific name of R. <p>
         * </ol>
         */
        public  Table SYSTEM_VIEW_ROUTINE_USAGE()
        {

            Table t = sysTables[_SYSTEM_VIEW_ROUTINE_USAGE];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_VIEW_ROUTINE_USAGE]);

                addColumn(t, "TABLE_CATALOG", Types.VARCHAR);
                addColumn(t, "TABLE_SCHEMA", Types.VARCHAR);
                addColumn(t, "TABLE_NAME", Types.VARCHAR, true);       // not null
                addColumn(t, "SPECIFIC_CATALOG", Types.VARCHAR);
                addColumn(t, "SPECIFIC_SCHEMA", Types.VARCHAR);
                addColumn(t, "SPECIFIC_NAME", Types.VARCHAR, true);    // not null

                // false PK, as VIEW_CATALOG, VIEW_SCHEMA, TABLE_CATALOG, and/or
                // TABLE_SCHEMA may be NULL
                t.createPrimaryKey(null, new int[] {
                0, 1, 2, 3, 4, 5
            }, false);

                return t;
            }

            // Calculated column values
            string viewCat;
            string viewSchem;
            string viewName;

            // Intermediate holders
            IEnumerable<Table> tables;
            View view;
            Table table;
            Object[] row;
            SubQuery[] subqueries;
            //Select select;
            Expression expression;
            Function function;
            Expression.Collector collector;
            MethodInfo method;
            HashSet<MethodInfo> methodSet;
            //Iterator iterator;

            // Column number mappings
            const int iv_cat = 0;
            const int iv_schem = 1;
            const int iv_name = 2;
            const int ir_cat = 3;
            const int ir_schem = 4;
            const int ir_name = 5;
            //const int ir_sig = 6;

            // Initialization
            tables = database.schemaManager.allTablesIterator();
            collector = new Expression.Collector();

            // Do it.
            foreach (var temp in tables)
            {
                collector.Clear();

                table = (Table)temp;

                if (table.isView() && isAccessibleTable(table))
                {

                    // fall through
                }
                else
                {
                    continue;
                }

                viewCat = ns.getCatalogName(table);
                viewSchem = table.getSchemaName();
                viewName = table.getName().name;
                view = (View)table;
                subqueries = view.viewSubqueries;

                collector.addAll(view.viewSelect, Expression.FUNCTION);

                for (int i = 0; i < subqueries.Length; i++)
                {
                    collector.addAll(subqueries[i].select, Expression.FUNCTION);
                }

                methodSet = new HashSet<MethodInfo>();


                foreach (var expr in collector)
                {
                    expression = (Expression)expr;
                    function = expression.function;

                    if (session.isAccessible(
                            function.getMethod().DeclaringType.Name))
                    {
                        methodSet.Add(function.getMethod());
                    }
                }



                foreach (var m in methodSet)
                {
                    method = m;
                    row = t.getEmptyRowData();
                    row[iv_cat] = viewCat;
                    row[iv_schem] = viewSchem;
                    row[iv_name] = viewName;
                    row[ir_cat] = ns.getCatalogName(method);
                    row[ir_schem] = ns.getSchemaName(method);
                    row[ir_name] = DINameSpace.getMethodSpecificName(method);

                    t.insertSys(row);
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * Inserts a set of procedure column description rows into the
         * <code>Table</code> specified by the <code>t</code> argument. <p>
         *
         * @param t the table _in which the rows are to be inserted
         * @param l the list of procedure name aliases to which the
         *        specified column values apply
         * @param cat the procedure's catalog name
         * @param schem the procedure's schema name
         * @param pName the procedure's simple base (non-alias) name
         * @param cName the procedure column name
         * @param cType the column type (return, parameter, result)
         * @param dType the column's data type code
         * @param tName the column's canonical data type name
         * @param prec the column's precision
         * @param len the column's buffer length
         * @param scale the column's scale (decimal digits)
         * @param radix the column's numeric precision radix
         * @param nullability the column's java.sql.DatbaseMetaData
         *      nullabiliy code
         * @param remark a human-readable remark regarding the column
         * @param specificName the specific name of the procedure
         *      (typically but not limited to
         *      a fully qualified Java Method name and signature)
         * @param seq helper value to back JDBC contract sort order
         * @  if there is problem inserting the specified rows
         *      _in the table
         *
         */
        protected void addPColRows(Table t, List<string> l, string cat,
                                   string schem, string pName, string cName,
                                   int cType, int dType, string tName,
                                   int? prec, int? len, int? scale,
                                   int? radix, int nullability,
                                   string remark, string specificName,
                                   int seq)
        {

            // column number mappings
            const int icat = 0;
            const int ischem = 1;
            const int iname = 2;
            const int icol_name = 3;
            const int icol_type = 4;
            const int idata_type = 5;
            const int itype_name = 6;
            const int iprec = 7;
            const int ilength = 8;
            const int iscale = 9;
            const int iradix = 10;
            const int inullable = 11;
            const int iremark = 12;
            const int isn = 13;
            const int iseq = 14;
            Object[] row = t.getEmptyRowData();
            int sequence = (seq);

            row[icat] = cat;
            row[ischem] = schem;
            row[iname] = pName;
            row[icol_name] = cName;
            row[icol_type] = cType;
            row[idata_type] = dType;
            row[itype_name] = tName;
            row[iprec] = prec;
            row[ilength] = len;
            row[iscale] = scale;
            row[iradix] = radix;
            row[inullable] = nullability;
            row[iremark] = remark;
            row[isn] = specificName;
            row[iseq] = sequence;

            t.insertSys(row);

            if (l != null)
            {
                int size = l.Count;

                for (int i = 0; i < size; i++)
                {
                    row = t.getEmptyRowData();
                    pName = (String)l[i];
                    row[icat] = cat;
                    row[ischem] = schem;
                    row[iname] = pName;
                    row[icol_name] = cName;
                    row[icol_type] = cType;
                    row[idata_type] = dType;
                    row[itype_name] = tName;
                    row[iprec] = prec;
                    row[ilength] = len;
                    row[iscale] = scale;
                    row[iradix] = radix;
                    row[inullable] = nullability;
                    row[iremark] = remark;
                    row[isn] = specificName;
                    row[iseq] = sequence;

                    t.insertSys(row);
                }
            }
        }

        /**
         * Inserts a set of procedure description rows into the <code>Table</code>
         * object specified by the <code>t</code> argument. <p>
         *
         * @param t the table into which the specified rows will eventually
         *      be inserted
         * @param l the list of procedure name aliases to which the specified column
         *      values apply
         * @param cat the procedure catalog name
         * @param schem the procedure schema name
         * @param pName the base (non-alias) procedure name
         * @param ip the procedure input parameter count
         * @param op the procedure output parameter count
         * @param rs the procedure result column count
         * @param remark a human-readable remark regarding the procedure
         * @param pType the procedure type code, indicating whether it is a
         *      function, procedure, or uncatagorized (i.e. returns
         *      a value, does not return a value, or it is unknown
         *      if it returns a value)
         * @param origin origin of the procedure, e.g.
         *      (["BUILTIN" | "USER DEFINED"] "ROUTINE" | "TRIGGER") | "ALIAS", etc.
         * @param specificName the specific name of the procedure
         *      (typically but not limited to a
         *      fully qualified Java Method name and signature)
         * @  if there is problem inserting the specified rows
         *      _in the table
         *
         */
        protected void addProcRows(Table t, List<string> l, string cat,
                                   string schem, string pName, int ip,
                                   int op, int rs, string remark,
                                   int pType, string origin,
                                   string specificName)
        {

            // column number mappings
            const int icat = 0;
            const int ischem = 1;
            const int ipname = 2;
            const int iinput_parms = 3;
            const int ioutput_parms = 4;
            const int iresult_sets = 5;
            const int iremark = 6;
            const int iptype = 7;
            const int iporigin = 8;
            const int isn = 9;
            Object[] row = t.getEmptyRowData();

            row[icat] = cat;
            row[ischem] = schem;
            row[ipname] = pName;
            row[iinput_parms] = ip;
            row[ioutput_parms] = op;
            row[iresult_sets] = rs;
            row[iremark] = remark;
            row[iptype] = pType;
            row[iporigin] = origin;
            row[isn] = specificName;

            t.insertSys(row);

            if (l != null)
            {
                int size = l.Count;

                for (int i = 0; i < size; i++)
                {
                    row = t.getEmptyRowData();
                    pName = (String)l[i];
                    row[icat] = cat;
                    row[ischem] = schem;
                    row[ipname] = pName;
                    row[iinput_parms] = ip;
                    row[ioutput_parms] = op;
                    row[iresult_sets] = rs;
                    row[iremark] = remark;
                    row[iptype] = pType;
                    row[iporigin] = "ALIAS";
                    row[isn] = specificName;

                    t.insertSys(row);
                }
            }
        }

        //------------------------------------------------------------------------------
        // boucherb@users 20050515 further SQL2003 metadata support

        /**
         *  SYSTEM_AUTHORIZATIONS<p>
         *
         *  <b>Function</b><p>
         *
         *  The AUTHORIZATIONS table has one row for each &lt;role name&gt; and
         *  one row for each &lt;authorization identifier &gt; referenced _in the
         *  Information Schema. These are the &lt;role name&gt;s and
         *  &lt;authorization identifier&gt;s that may grant privileges as well as
         *  those that may create a schema, or currently own a schema created
         *  through a &lt;schema definition&gt;. <p>
         *
         *  <b>Definition</b><p>
         *
         *  <pre class="SqlCodeExample">
         *  CREATE TABLE AUTHORIZATIONS (
         *       AUTHORIZATION_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *       AUTHORIZATION_TYPE INFORMATION_SCHEMA.CHARACTER_DATA
         *           CONSTRAINT AUTHORIZATIONS_AUTHORIZATION_TYPE_NOT_NULL
         *               NOT NULL
         *           CONSTRAINT AUTHORIZATIONS_AUTHORIZATION_TYPE_CHECK
         *               CHECK ( AUTHORIZATION_TYPE IN ( 'USER', 'ROLE' ) ),
         *           CONSTRAINT AUTHORIZATIONS_PRIMARY_KEY
         *               PRIMARY KEY (AUTHORIZATION_NAME)
         *       )
         *  </pre>
         *
         *  <b>Description</b><p>
         *
         *  <ol>
         *  <li> The values of AUTHORIZATION_TYPE have the following meanings:<p>
         *
         *  <table border cellpadding="3">
         *       <tr>
         *           <td nowrap>USER</td>
         *           <td nowrap>The value of AUTHORIZATION_NAME is a known
         *                      &lt;user identifier&gt;.</td>
         *       <tr>
         *       <tr>
         *           <td nowrap>NO</td>
         *           <td nowrap>The value of AUTHORIZATION_NAME is a &lt;role
         *                      name&gt; defined by a &lt;role definition&gt;.</td>
         *       <tr>
         *  </table> <p>
         *  </ol>
         */
        public Table SYSTEM_AUTHORIZATIONS()
        {

            Table t = sysTables[_SYSTEM_AUTHORIZATIONS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_AUTHORIZATIONS]);

                addColumn(t, "AUTHORIZATION_NAME", Types.VARCHAR, true);    // not null
                addColumn(t, "AUTHORIZATION_TYPE", Types.VARCHAR, true);    // not null

                // true PK
                t.createPrimaryKey(null, new int[] { 0 }, true);

                return t;
            }

            // Intermediate holders
            List<User> users;
            IEnumerable<string> roles;
            User user;
            int userCount;
            Object[] row;

            // Initialization
            users = database.getUserManager().listVisibleUsers(session, false);
            userCount = users.Count;

            // Do it.
            for (int i = 0; i < users.Count; i++)
            {
                row = t.getEmptyRowData();
                user = (User)users[i];
                row[0] = user.getName();
                row[1] = "USER";

                t.insertSys(row);
            }

            roles = database.getGranteeManager().getRoleNames();

            foreach (var r in roles)
            {
                row = t.getEmptyRowData();
                row[0] = r.ToString();
                row[1] = "ROLE";

                t.insertSys(row);
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * SYSTEM_COLLATIONS<p>
         *
         * <b>Function<b><p>
         *
         * The COLLATIONS table has one row for each character collation
         * descriptor. <p>
         *
         * <b>Definition</b>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE COLLATIONS (
         *      COLLATION_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      COLLATION_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      COLLATION_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      PAD_ATTRIBUTE INFORMATION_SCHEMA.CHARACTER_DATA
         *          CONSTRAINT COLLATIONS_PAD_ATTRIBUTE_CHECK
         *              CHECK ( PAD_ATTRIBUTE IN
         *                  ( 'NO PAD', 'PAD SPACE' ) ),
         *      COLLATION_TYPE INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      COLLATION_DEFINITION INFORMATION_SCHEMA.CHARACTER_DATA,
         *      COLLATION_DICTIONARY INFORMATION_SCHEMA.CHARACTER_DATA,
         *      CHARACTER_REPERTOIRE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER
         *          CONSTRAINT CHARACTER_REPERTOIRE_NAME_NOT_NULL
         *              NOT NULL,
         *      CONSTRAINT COLLATIONS_PRIMARY_KEY
         *          PRIMARY KEY ( COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME ),
         *      CONSTRAINT COLLATIONS_FOREIGN_KEY_SCHEMATA
         *          FOREIGN KEY ( COLLATION_CATALOG, COLLATION_SCHEMA )
         *              REFERENCES SCHEMATA
         * )
         * </pre>
         *
         * <b>Description</b><p>
         *
         * <ol>
         *      <li>The values of COLLATION_CATALOG, COLLATION_SCHEMA, and
         *          COLLATION_NAME are the catalog name, unqualified schema name,
         *          and qualified identifier, respectively, of the collation being
         *          described.<p>
         *
         *      <li>The values of COLLATION_TYPE, COLLATION_DICTIONARY, and
         *          COLLATION_DEFINITION are the null value (deprectated). <p>
         *
         *      <li>The values of PAD_ATTRIBUTE have the following meanings:<p>
         *
         *      <table border cellpadding="3">
         *          <tr>
         *              <td nowrap>NO PAD</td>
         *              <td nowrap>The collation being described has the NO PAD
         *                  characteristic.</td>
         *          <tr>
         *          <tr>
         *              <td nowrap>PAD</td>
         *              <td nowrap>The collation being described has the PAD SPACE
         *                         characteristic.</td>
         *          <tr>
         *      </table> <p>
         *
         *      <li>The value of CHARACTER_REPERTOIRE_NAME is the name of the
         *          character repertoire to which the collation being described
         *          is applicable.
         * </ol>
         */
        public Table SYSTEM_COLLATIONS()
        {

            Table t = sysTables[_SYSTEM_COLLATIONS];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_COLLATIONS]);

                addColumn(t, "COLLATION_CATALOG", Types.VARCHAR);
                addColumn(t, "COLLATION_SCHEMA", Types.VARCHAR, true);
                addColumn(t, "COLLATION_NAME", Types.VARCHAR, true);
                addColumn(t, "PAD_ATTRIBUTE", Types.VARCHAR, 9, true);
                addColumn(t, "COLLATION_TYPE", Types.VARCHAR, true);
                addColumn(t, "COLLATION_DEFINITION", Types.VARCHAR);
                addColumn(t, "COLLATION_DICTIONARY", Types.VARCHAR);
                addColumn(t, "CHARACTER_REPERTOIRE_NAME", Types.VARCHAR, true);

                // false PK, as rows may have NULL COLLATION_CATALOG
                t.createPrimaryKey(null, new int[] {
                0, 1, 2
            }, false);

                return t;
            }

            IEnumerable<string> collations;
            string collation;
            string collationSchema = SchemaManager.PUBLIC_SCHEMA;
            string padAttribute = "NO PAD";
            string characterRepertoireName = "UNICODE";
            Object[] row;
            const int icolcat = 0;
            const int icolschem = 1;
            const int icolname = 2;
            const int ipadattr = 3;
            //const int icoltype = 4;
            //const int icoldef = 5;
            //const int icoldict = 6;
            const int icharrep = 7;

            collations = Collation.nameToJavaName.Keys;

            foreach (var c in collations)
            {
                row = t.getEmptyRowData();
                collation = (String)c;
                row[icolcat] = ns.getCatalogName(collation);
                row[icolschem] = collationSchema;
                row[icolname] = collation;
                row[ipadattr] = padAttribute;
                row[icharrep] = characterRepertoireName;

                t.insertSys(row);
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * SYSTEM_ENABLED_ROLES<p>
         *
         * <b>Function</b><p>
         *
         * Identify the enabled roles for the current SQL-session.<p>
         *
         * Definition<p>
         *
         * <pre class="SqlCodeExample">
         * CREATE RECURSIVE VIEW ENABLED_ROLES ( ROLE_NAME ) AS
         *      VALUES ( CURRENT_ROLE )
         *      UNION
         *      SELECT RAD.ROLE_NAME
         *        FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS RAD
         *        JOIN ENABLED_ROLES R
         *          ON RAD.GRANTEE = R.ROLE_NAME;
         *
         * GRANT SELECT ON TABLE ENABLED_ROLES
         *    TO PUBLIC WITH GRANT OPTION;
         * </pre>
         */

        /**
         * SYSTEM_APPLICABLE_ROLES<p>
         *
         * <b>Function</b><p>
         *
         * Identifies the applicable roles for the current user.<p>
         *
         * <b>Definition</b><p>
         *
         * <pre class="SqlCodeExample">
         * CREATE RECURSIVE VIEW APPLICABLE_ROLES ( GRANTEE, ROLE_NAME, IS_GRANTABLE ) AS
         *      ( ( SELECT GRANTEE, ROLE_NAME, IS_GRANTABLE
         *            FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS
         *           WHERE ( GRANTEE IN ( CURRENT_USER, 'PUBLIC' )
         *                OR GRANTEE IN ( SELECT ROLE_NAME
         *                                  FROM ENABLED_ROLES ) ) )
         *      UNION
         *      ( SELECT RAD.GRANTEE, RAD.ROLE_NAME, RAD.IS_GRANTABLE
         *          FROM DEFINITION_SCHEMA.ROLE_AUTHORIZATION_DESCRIPTORS RAD
         *          JOIN APPLICABLE_ROLES R
         *            ON RAD.GRANTEE = R.ROLE_NAME ) );
         *
         * GRANT SELECT ON TABLE APPLICABLE_ROLES
         *    TO PUBLIC WITH GRANT OPTION;
         * </pre>
         */

        /**
         * SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS<p>
         *
         * <b>Function</b><p>
         *
         * Contains a representation of the role authorization descriptors.<p>
         *
         * <b>Definition</b>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE ROLE_AUTHORIZATION_DESCRIPTORS (
         *      ROLE_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      GRANTEE INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      GRANTOR INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      IS_GRANTABLE INFORMATION_SCHEMA.CHARACTER_DATA
         *          CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_IS_GRANTABLE_CHECK
         *              CHECK ( IS_GRANTABLE IN
         *                  ( 'YES', 'NO' ) ),
         *          CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_PRIMARY_KEY
         *              PRIMARY KEY ( ROLE_NAME, GRANTEE ),
         *          CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_CHECK_ROLE_NAME
         *              CHECK ( ROLE_NAME IN
         *                  ( SELECT AUTHORIZATION_NAME
         *                      FROM AUTHORIZATIONS
         *                     WHERE AUTHORIZATION_TYPE = 'ROLE' ) ),
         *          CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_FOREIGN_KEY_AUTHORIZATIONS_GRANTOR
         *              FOREIGN KEY ( GRANTOR )
         *                  REFERENCES AUTHORIZATIONS,
         *          CONSTRAINT ROLE_AUTHORIZATION_DESCRIPTORS_FOREIGN_KEY_AUTHORIZATIONS_GRANTEE
         *              FOREIGN KEY ( GRANTEE )
         *                  REFERENCES AUTHORIZATIONS
         *      )
         * </pre>
         *
         * <b>Description</b><p>
         *
         * <ol>
         *      <li>The value of ROLE_NAME is the &lt;role name&gt; of some
         *          &lt;role granted&gt; by the &lt;grant role statement&gt; or
         *          the &lt;role name&gt; of a &lt;role definition&gt;. <p>
         *
         *      <li>The value of GRANTEE is an &lt;authorization identifier&gt;,
         *          possibly PUBLIC, or &lt;role name&gt; specified as a
         *          &lt;grantee&gt; contained _in a &lt;grant role statement&gt;,
         *          or the &lt;authorization identifier&gt; of the current
         *          SQLsession when the &lt;role definition&gt; is executed. <p>
         *
         *      <li>The value of GRANTOR is the &lt;authorization identifier&gt;
         *          of the user or role who granted the role identified by
         *          ROLE_NAME to the user or role identified by the value of
         *          GRANTEE. <p>
         *
         *      <li>The values of IS_GRANTABLE have the following meanings:<p>
         *
         *      <table border cellpadding="3">
         *          <tr>
         *              <td nowrap>YES</td>
         *              <td nowrap>The described role is grantable.</td>
         *          <tr>
         *          <tr>
         *              <td nowrap>NO</td>
         *              <td nowrap>The described role is not grantable.</td>
         *          <tr>
         *      </table> <p>
         * </ol>
         */
        public  Table SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS()
        {

            Table t = sysTables[_SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS];

            if (t == null)
            {
                t = createBlankTable(
                    sysTableHsqlNames[_SYSTEM_ROLE_AUTHORIZATION_DESCRIPTORS]);

                addColumn(t, "ROLE_NAME", Types.VARCHAR, true);       // not null
                addColumn(t, "GRANTEE", Types.VARCHAR, true);         // not null
                addColumn(t, "GRANTOR", Types.VARCHAR, true);         // not null
                addColumn(t, "IS_GRANTABLE", Types.VARCHAR, true);    // not null

                // true PK
                t.createPrimaryKey(null, new int[] {
                0, 1
            }, true);

                return t;
            }

            // Intermediate holders
            string grantorName = GranteeManager.SYSTEM_AUTHORIZATION_NAME;
            IEnumerable<Grantee> grantees;
            Grantee grantee;
            string granteeName;
            IEnumerable<string> roles;
            string roleName;
            string isGrantable;
            Object[] row;
            const int irole = 0;
            const int igrantee = 1;
            const int igrantor = 2;
            const int igrantable = 3;

            // Initialization
            grantees = database.getGranteeManager().getGrantees();

            // Do it.
            foreach (var g in grantees)
            {
                grantee = (Grantee)g;
                granteeName = grantee.getName();
                roles = grantee.getDirectRoles();

                foreach (var r in roles)
                {
                    row = t.getEmptyRowData();
                    roleName = (String)r;
                    isGrantable =
                        grantee.hasRole(GranteeManager.DBA_ADMIN_ROLE_NAME) ? "YES"
                                                                            : "NO";
                    row[irole] = roleName;
                    row[igrantee] = granteeName;
                    row[igrantor] = grantorName;
                    row[igrantable] = isGrantable;

                    t.insertSys(row);
                }
            }

            t.setDataReadOnly(true);

            return t;
        }

        /**
         * SYSTEM_SCHEMATA<p>
         *
         * <b>Function</b><p>
         *
         * The SCHEMATA table has one row for each schema. <p>
         *
         * <b>Definition</b><p>
         *
         * <pre class="SqlCodeExample">
         * CREATE TABLE SCHEMATA (
         *      CATALOG_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      SCHEMA_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER,
         *      SCHEMA_OWNER INFORMATION_SCHEMA.SQL_IDENTIFIER
         *          CONSTRAINT SCHEMA_OWNER_NOT_NULL
         *              NOT NULL,
         *      DEFAULT_CHARACTER_SET_CATALOG INFORMATION_SCHEMA.SQL_IDENTIFIER
         *          CONSTRAINT DEFAULT_CHARACTER_SET_CATALOG_NOT_NULL
         *              NOT NULL,
         *      DEFAULT_CHARACTER_SET_SCHEMA INFORMATION_SCHEMA.SQL_IDENTIFIER
         *          CONSTRAINT DEFAULT_CHARACTER_SET_SCHEMA_NOT_NULL
         *              NOT NULL,
         *      DEFAULT_CHARACTER_SET_NAME INFORMATION_SCHEMA.SQL_IDENTIFIER
         *          CONSTRAINT DEFAULT_CHARACTER_SET_NAME_NOT_NULL
         *              NOT NULL,
         *      SQL_PATH INFORMATION_SCHEMA.CHARACTER_DATA,
         *
         *      CONSTRAINT SCHEMATA_PRIMARY_KEY
         *          PRIMARY KEY ( CATALOG_NAME, SCHEMA_NAME ),
         *      CONSTRAINT SCHEMATA_FOREIGN_KEY_AUTHORIZATIONS
         *          FOREIGN KEY ( SCHEMA_OWNER )
         *              REFERENCES AUTHORIZATIONS,
         *      CONSTRAINT SCHEMATA_FOREIGN_KEY_CATALOG_NAMES
         *          FOREIGN KEY ( CATALOG_NAME )
         *              REFERENCES CATALOG_NAMES
         *      )
         * </pre>
         *
         * <b>Description</b><p>
         *
         * <ol>
         *      <li>The value of CATALOG_NAME is the name of the catalog of the
         *          schema described by this row.<p>
         *
         *      <li>The value of SCHEMA_NAME is the unqualified schema name of
         *          the schema described by this row.<p>
         *
         *      <li>The values of SCHEMA_OWNER are the authorization identifiers
         *          that own the schemata.<p>
         *
         *      <li>The values of DEFAULT_CHARACTER_SET_CATALOG,
         *          DEFAULT_CHARACTER_SET_SCHEMA, and DEFAULT_CHARACTER_SET_NAME
         *          are the catalog name, unqualified schema name, and qualified
         *          identifier, respectively, of the default character set for
         *          columns and domains _in the schemata.<p>
         *
         *      <li>Case:<p>
         *          <ul>
         *              <li>If &lt;schema path specification&gt; was specified in
         *                  the &lt;schema definition&gt; that defined the schema
         *                  described by this row and the character representation
         *                  of the &lt;schema path specification&gt; can be
         *                  represented without truncation, then the value of
         *                  SQL_PATH is that character representation.<p>
         *
         *              <li>Otherwise, the value of SQL_PATH is the null value.
         *         </ul>
         * </ol>
         */
        public  Table SYSTEM_SCHEMATA()
        {

            Table t = sysTables[_SYSTEM_SCHEMATA];

            if (t == null)
            {
                t = createBlankTable(sysTableHsqlNames[_SYSTEM_SCHEMATA]);

                addColumn(t, "CATALOG_NAME", Types.VARCHAR);
                addColumn(t, "SCHEMA_NAME", Types.VARCHAR, true);
                addColumn(t, "SCHEMA_OWNER", Types.VARCHAR, true);
                addColumn(t, "DEFAULT_CHARACTER_SET_CATALOG", Types.VARCHAR);
                addColumn(t, "DEFAULT_CHARACTER_SET_SCHEMA", Types.VARCHAR, true);
                addColumn(t, "DEFAULT_CHARACTER_SET_NAME", Types.VARCHAR);
                addColumn(t, "SQL_PATH", Types.VARCHAR);

                // order: CATALOG_NAME, SCHEMA_NAME
                // false PK, as rows may have NULL CATALOG_NAME
                t.createPrimaryKey(null, new int[] {
                0, 1
            }, false);

                return t;
            }

            IEnumerable<string> schemas;
            string schema;
            string schemaOwner = GranteeManager.DBA_ADMIN_ROLE_NAME;
            string dcsSchema = SchemaManager.INFORMATION_SCHEMA;
            string dcsName = ("UTF16");
            string sqlPath = null;
            Object[] row;
            const int ischema_catalog = 0;
            const int ischema_name = 1;
            const int ischema_owner = 2;
            const int idef_charset_cat = 3;
            const int idef_charset_schem = 4;
            const int idef_charset_name = 5;
            const int isql_path = 6;

            // Initialization
            schemas = database.schemaManager.fullSchemaNamesIterator();

            // Do it.
            foreach (var s in schemas)
            {
                row = t.getEmptyRowData();
                schema = (String)s;
                row[ischema_catalog] = ns.getCatalogName(schema);
                row[ischema_name] = schema;
                row[ischema_owner] = schemaOwner;
                row[idef_charset_cat] = ns.getCatalogName(dcsSchema);
                row[idef_charset_schem] = dcsSchema;
                row[idef_charset_name] = dcsName;
                row[isql_path] = sqlPath;

                t.insertSys(row);
            }

            t.setDataReadOnly(true);

            return t;
        }
    }
}
